SQl server authentication issue in C#

Hi Guys,

I have a tool written in C# which connects to a remote SQl server 2005. Its working fine while I use "Integrated Security". However, I want to login the database using another credential instead of using the currently credentials associated with the tool. The reason for this was because not all the users using this tool have permission to access the database. So, I need sort of impersonation to allow any user using this tool to connect to the database.

Is there any simple and efficnet way to do this Thanks.

Answer this question

SQl server authentication issue in C#

yarrick
I don't know if you have SQL Server Management Studio, its a really good graphical tool. Use that tool and create a new database and name it say test...

Create a new User : say (test1) and some password : say (testingconnection) and make test1 owner of test database...

now try using this connection string and see what happens :

Data Source=localhost or address of the machine;Initial Catalog=test;User Id=test1;Password=testingconnection;
slledru
yeah, thats exactly what I tried at the beginning. But an error occured while the connection was being openned. I checked the server error log and it gave me:

Source Logon

Message
Login failed for user 'domain\user'. [CLIENT: x.x.x.x]

Error: 18456, Severity: 14, State: 6.

I also checked MSDN for state=6.

State Description

2

User ID is not valid.

5

User ID is not valid.

6

An attempt was made to use a Windows login name with SQL Server Authentication.
Sorin Sandu
it should not be the same as windows user account but same as the user account which u have created in sql server and granted access to
cb3431

Hi

the username & password in the connectionstring needs to be the same as an sql server user (with sql server authentication)

if you want to use the domain account you should impersonate the aforementionned user when you access the database

Hope this helps, please close the thread if it does
JavaBoy

Hi

please close this thread by clicking the "was this post helpful" yes button on the answers that helped you out
Gurpreet Singh Gill
I have checked the configuration of SQL server 2005. Both integrated and SQl server aythentication are enabled by default/ So, I didn't change anything.

My question is : the user id/password pair in connection stirng is the same as the windows login username/password pair
DQM
You can use this line as a Connection String and should do it

Data Source=localhost or address of the machine;Initial Catalog=NameofDatabase;User Id=username;Password=password;
GustavoPollitzer
Initially, I used "Integrated security" option in the connection string to open the connection with remote SQL server. It worked fine. So, that means windows login has been enabled. Then, I swicthed to use the username/password pair of the same account, the error just occured.
Remmie
When you setup your database did you opted for both SQL and Windows Authentication or just one..
Aaron Leiby
Thank you very much for you guys suggestions.

I have figured it out that the login failure was due to my confusing "windows authentication account" with "sql authentication account". I was not aware that I was providing windows account in the "connection string". Now, after I created a new "sql authentication account", the connection issue was resolved.

Thanks again!
Jocchan
that means, sql server authentication is not enabled. Do you mean I just need to enable both authentications Let me try. But is this a good practise

Thanks
SQl server authentication issue in C#

Answer this question