C# - connection to a remote SQL server

[Guest]

hi
I have big problem. I am writing a small win application. It will work on
remote MS SQL database (2005). Firstly, I need to log on to the server. The
server supports remote connections and sql and windows authentication.
I declared connection string as follows:

conn = new SqlConnection("user
id=<user>;password=<pass>;server=<IP>,1433;database=<dbname>;Trusted_connection=yes;connection timeout=3");

Using that string I can log on ALWAYS. there is no difference in user or
password. Connection is always successful.
If I remove trusted_connection=yes I will be able to connect to only as 'sa'
user. NOTHING else.

I have tested that only on my own sql server.
But my question is: what is wrong??? I would like to log on only for certain
accounts BUT not for all or just for one

 

[Steven Nagy]

Possibly the PC you are accessing from has an access Token from the
remove server (not sql server, but win nt)
When you turn on 'trusted', it is my understanding that it is relying
on your win nt authentication, and doesn't actually care what you pass
as a user name and password. You probably need to decide whether you
want to authenticate with trusted auth (nt) or sql auth. If you want to
test with trusted auth, try creating another account on your pc, log
off and on again and run your app. It shouldn't get access because that
new account probably doesn't have access to the remote pc, and won't be
storing a token for access to the remote pc. But if you try to connect
to the remote pc by file share or enterprise manager then this will get
a nt token and then your app is automatically authenticated while
running under the current user.
I'm not sure if this is your exact problem though, and I don't
understand a lot about nt authentication and tokens. So all in all it
could be a really crap answer!

Can you not just create an SQL user (not nt user) on the remote
instance of SQL Server, give it access to your DB, and turn off
'trusted' in your connection string, using the new account? This would
probably be best, unless you expect every user who uses your
application to also have an NT account on the remote server.

Hope this helps and isn't too wishy washy. Have a good day!
Steve