CODEWITHSUNDEEP
c#sql-serversqlconnection
Neeru
Neeru

Reputation: 71

sql connection string using sql authentication

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.

Upvotes: 7

Views: 60734

Answers (5)

Ronsell Fortu
Ronsell Fortu

Reputation: 1

I just added the "Encrypt=True" and it worked.

FROM: "Server=LAPTOP-D82MD6F3\MSSQLSERVER2022;Database=SchoolDb;User Id=sa;Password=@Test123;TrustServerCertificate=True;"

TO: FROM: "Server=LAPTOP-D82MD6F3\MSSQLSERVER2022;Database=SchoolDb;User Id=sa;Password=@Test123;Encrypt=True;TrustServerCertificate=True;"

Upvotes: 0

Saqlain Mushtaq
Saqlain Mushtaq

Reputation: 193

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

Upvotes: 0

Jeff Codes
Jeff Codes

Reputation: 77

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.

Upvotes: 3

Hassanuzzaman
Hassanuzzaman

Reputation: 119

I think you can you use this

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

Upvotes: 8

Brian Rudolph
Brian Rudolph

Reputation: 6318

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.

Upvotes: 3

Related Questions