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.

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> — deostroll
– deostroll, Commented Sep 4, 2012 at 14:01

Hassanuzzaman · Accepted Answer · 2016-07-20 09:26:02Z

8

I think you can you use this

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

answered Jul 20, 2016 at 9:26

Hassanuzzaman

1191 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dakab Over a year ago

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

Aetos2501 Over a year ago

Funciono para mi

Brian Rudolph · Accepted Answer · 2012-09-04 14:00:26Z

3

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.

answered Sep 4, 2012 at 14:00

Brian Rudolph

6,3282 gold badges26 silver badges19 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:17:25Z

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.

Saqlain Mushtaq · Accepted Answer · 2021-10-07 09:42:16Z

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

Ronsell Fortu · Accepted Answer · 2024-11-28 13:50:32Z

0

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;"

answered Nov 28, 2024 at 13:50

Ronsell Fortu

11 bronze badge

Collectives™ on Stack Overflow

sql connection string using sql authentication

5 Answers 5

2 Comments

Comments

Comments

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

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

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

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related