User permission problems. sqlconnection

  • Thread starter Thread starter sflynn
  • Start date Start date
S

sflynn

I've just created a new program in Visual Studio 2005, Visual Basic.
I can publish, install, and run the program just fine as long as the
user is a member of the "Domain Admins" group. My problem is when a
non-admin using tries to run the program. The program gets hung up on
the following line

Dim cn As System.Data.SqlClient.SqlConnection = New SqlConnection("Data
Source=spdb01;Initial Catalog=SToP;Integrated Security=True;")

How can I fix this, or what is the problem. I've been pulling my hair
out over this. I'm sure it's not the permissions on the SQL server,
I've givin the "Domain Users" group full control of the database.
 
You said "I've givin the "Domain Users" group full control of the database"

then

"My problem is when a non-admin using tries to run the program. "

Ding ding? Non-admin users can't connect to the database, obviously. Add
them.

Any reason you need Integrated Security on SQL? You could pass a
username/password in the connection string instead

http://www.connectionstrings.com

Jeff
 
Jeff,

In my idea is your first solution enough, if the user has no permissions,
than adding the username/password in the connection string has as well not
sense.
"My problem is when a non-admin using tries to run the program. "

Ding ding? Non-admin users can't connect to the database, obviously. Add
them.

Any reason you need Integrated Security on SQL? You could pass a
username/password in the connection string instead
Click to expand...

Cor
 
I believe that Jeff means that a database user account would be easier
to handle than a trusted connection. Just add the user in the datbase
and supply the credentials in the connection string. Then you don't have
to grant access for a lot of windows user accounts to the database.

That also makes it easier to tighten the security. The user account can
be given permission to a single database, and only permission to execute
stored procedures. That would make the connection totally safe against
SQL injections (unless of course a stored procedure creates SQL
dynamically).
 
Thanks for the replies guys. I'll clarify my issue a little more.
All users in my domain are members of the "Domain Users", I've even
tried adding the specific user I'm testing with as db_owner of the
database, still nothing happens. When a non-admin user gets to the
line where I declare the SQLConnection, it just seems to stop, and then
the program starts eating up memory (using Task Manager) like crazy. I
let it run once until it used up over 200mb, normally the program only
uses 20mb. The program doesn't error out at all, and I've got that
line encased in a "Try, Catch" statement.
 
I also just tried passing a username and password to the database. The
admin users are still working, but the others are not. It seems that
the non-admin users don't have permissions to run "Sqlconnection", it
doesn't seem to be a database security problem, but a system security
problem somewhere.
 
Also, it works fine for any user on a Windows XP and 2000 workstation,
but not for a regular user on a Windows 2000 Terminal Server.
 
What do you mean "not a database security problem"? Of course it is!

I'm surprised you aren't reading what you are writing. Did you see my first
reply? You've set up the admins as users in the database. That means NO ONE
ELSE can access it!

And that is exactly what you are seeing.

And did you even try my suggestion? Don't use Windows Security. Create a
single SQL user account, and put that in the (single) connection string. I
used to work in the SQL Server group at Microsoft, and wrote a book about
it. Trust me.

If you really need Windows Security, follow these steps:

* Ideally, put all the users you need to have access into a company global
domain group. You might have to ask your IT dept for this.

* Otherwise, create a local group on the SQL box. Then add your users to
this group.

* Then create a SQL login for this group, and give access to the appropriate
database.

However, if you don't need this much security, create a single SQL account
(not a Windows account), and use that single account, and pass the username
and password on the connection string. You may need to go through SQL Server
setup to tell it to use BOTH SQL and Windows authentication

Jeff
 
Also, just run Query Analyer. Have a non-admin user log onto your
workstation, and have them start up Query Analyzer, and try to connect to
the database. They won't be able to. So it's not in your app, it's in SQL
security.

Jeff
 
I've tried using the non integrated SQL security, and passing the
username and password. On Windows XP/ 2000 workstatiosn it works fine
as both an admin and regular user using integrated and SQL security.
But when a user tries to log in from a Windows 2000 Terminal Server, it
doesn't work. When I make that user a member of the local
Administrators group on the terminal server (not domain admin) it also
works.
 
Jeff,

But that is much more dangerous than permitting the domainusers in SQL
server the use of a table in the databaseserver and than to use integrated
security.

Whit what you suppose everybody can use the database. (Which can be in some
circumstances withouth problem ). However here the OP talks about his domain
users.

Cor
 
No, actually NOBODY can use the database, except the single logon user

But yes, EVERYBODY who uses the app, can access the database, under that
same account.

But I didn't read carefully enough, he has problems with Terminal Server
users.

Jeff
 
Jeff,
But I didn't read carefully enough, he has problems with Terminal Server
users.
Click to expand...
Where, just at his latest replies while this is probably the most relevant
information.

Cor
 
Back
Top