C# application connection problem with SQL Server 2005

[Mika M]

I've made application using C# 2005, which uses customer's SQL Server
2005. Application is working fine when user has administrator rights,
but application fails to connect to database when Windows XP user level
is normal user. I can't understand why normal users can't get
application to work, because ConnectionString is the same in both cases.
ConnectionString is like...

Data Source=MYSERVER\MYINSTANCE;Initial Catalog=MYDB;Persist Security
Info=True;User ID=MYUSER;Password=MYPASSWORD

My customer's computers are using same WORKGROUP - ie. no domain at all.
Any suggestions?

 

[Ignacio Machin ( .NET/ C# MVP )]

I've made application using C# 2005, which uses customer's SQL Server
2005. Application is working fine when user has administrator rights,
but application fails to connect to database when Windows XP user level
is normal user. I can't understand why normal users can't get
application to work, because ConnectionString is the same in both cases.
ConnectionString is like...

Data Source=MYSERVER\MYINSTANCE;Initial Catalog=MYDB;Persist Security
Info=True;User ID=MYUSER;Password=MYPASSWORD

My customer's computers are using same WORKGROUP - ie. no domain at all.
Any suggestions?

you have to grant permission to the users in the DB or use SQL
authentication

 

[Mika M]

Ignacio Machin ( .NET/ C# MVP ) kirjoitti:

you have to grant permission to the users in the DB or use SQL
authentication

Thanks - but how? SQL Server Management Studio's Treeview makes me
wonder where should I go and what to do, because SQL Server's user
management is complex to figure out how it really works. So I think many
users, like me, just tries changing something little and tests what is
the effect.

Maybe I have made something right because my connectionstring username &
password is working, and it gives access only to this database it uses.

 

[Paul]

My suggestion is to get your application to authenticate the user.

Then create a SQL user specifically for the DB. Lock this user down to
access to stored procedures only. i.e. no table or view access. Execute SP's
only.

This way everytime there is a new domain user/group you do not have to
change the security of your database.

A good implementation of CAS in your application helps too.

Some may think this is basic, but it is effective and secures any external
DB access from users to what they can do via SP's, and requires little
maintenance.