sql connection string using sql authentication

Question

I am using this sql connection string :

string connection = "data source=OSBORNECHARLES2;initial catalog=TWO;
integrated security=False;User ID=userid;Password=PWD";

I am getting this error :

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

If I set integrated security=True; it is working.
If I log in with another window user I'm getting error.

Can you please tell me why I'm getting this error.

Solution 1

I think you can you use this

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;

Solution 2

I am going to venture a guess here. I would assume that your instance of SQL is set to only allow Windows\Integrated logins. The userid\password you are setting in the connection string is for SQL logins only, you can't pass another windows user that way. It would appear that you are attempting to do impersonation using the connection string. I wish it was that simple.

So you likely either need to enable mixed mode security on your sql instance and create sql logins for this user, or you need to impersonate that windows user in your application and then use integrated security.

Solution 3

I know this is an old question, but what provider were you using. According to this post, 'SSPI' works with both, SQLClient and Oledb, whereas 'true'/'false' only works with SQLClient.

Here's another helpful post on this subject, which explains that using 'SSPI' or 'true' will result in it trying to use the windows credentials of the current user.

I agree with some of the other comments. Likely causes are: 1) SQL Authentication is not enabled on the server. Can do the following to troubleshoot: a. Try accessing using SQL auth connecting through SSMS. If it succeeds than you know SQL Authentication is enabled. 2) The user you're trying to login with that's failing doesn't have permission. a. Make sure the user has a server principal. As an extra measure (though I don't think it matters) ensure that server principal is tied to a database principal on their default database.

If it's a remote server you would need to enable all the necessary services, which can be done in SQL Server Configuration Manager.