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.

Antonietta

perhaps the user with which you are logging with doesn't have permissions for that db. Try connecting via management studio. And perhaps your instance is a named pipe instance. Hence the name may be like <machine>\<named-pipe> – Rondure

Answer 1

I think you can you use this

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

Referential

Why? What makes the difference? How specifically does it help with the error message? – Etching

Funciono para mi – Amphioxus

Answer 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.

B

Answer 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.

Exploitation

Answer 4

I faced the same issue and how I resolved it using the last query.

I know it is an old question but I'll post answer for new beginners if they are triggered with this error.

Not everyone uses Windows Authentication for their databases or server access or maybe the target server you want to access only allows SQL Server Authentication. In this case we simply need to remove the trusted connection part of the string and replace it with a userID and password:

select a.* from openrowset('SQLNCLI', 'Server=MYINSTANCE;UID=mySQLUser; PWD=*******;', 'select * from sys.databases') as a

If the error occured. Replace SQLCLI with MSDASQL and write driver name SQL Server.

select a.* from openrowset('MSDASQL', 'Driver={SQL SERVER}; Server=MYINSTANCE;UID=mySQLUser; PWD=*******;', 'select * from sys.databases') as a

Talanian