How to connect to MS SQL Server database from ASP using windows authentication specifying a windows user

Question

I've been trying for just over a month to connect an ASP script here to a SQL Server database but each time I use this connection string:
Data Source=dbServer01;Initial Catalog=POS123;Integrated Security=SSPI;User ID=domain\usr;Password=pwd;

It ignores the user I specify and takes the machine name to authenticate the connection, which obviously fails.

so I change the Integrated Security value to False, like this:
Data Source=dbServer01;Initial Catalog=POS123;Integrated Security=False;User ID=domain\usr;Password=pwd;

I then get an error: Login Failed for user "domain\usr" which is impossible because it works when we test the connection with it in the odbc admin app.

I asked the help of a senior and he said it's taking the user name as a database user name, but we need to make it use windows authentication, and specify which user to use. I remember reading about this a month ago and finding that there was no way to specify a user and password when connecting using windows authentication with this version of ASP.NET. I'm going to kill myself soon If I can't get this script to connect, someone save me please!

Community · Accepted Answer · 2017-05-23 12:29:53Z

3

You've got at least 2 3 options here:

Create an App Pool on your web server which runs as domain/usr and then assign your app to this app pool, and use integrated security. Your connection string will be Data Source=dbServer01;Initial Catalog=POS123;Integrated Security=SSPI; - i.e. drop the username and password - these are inherent in the AppPool's identity.
or (assuming Mixed Mode security is enabled) ask your DBA's to create a new SQL User (just called usr) with the same permissions as domain/usr and then change your connection string to standard security, with User Id=usr
If you enable impersonation (here and here), you can use the domain credential without changing the app pool identity. Note the point about securing cleartext passwords, and IME this typically also requires additional configuration to avoid the double-hop issue.

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Jul 9, 2013 at 11:34

StuartLC

108k18 gold badges225 silver badges294 bronze badges

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

4 Comments

pythonian29033 Over a year ago

I ran the default app pool as that user, it's not working, the error persists

StuartLC Over a year ago

It might be because you've set both SSPI and a UName/Password - this is incompatable - stackoverflow.com/questions/1229691/…. Also, double check, you've set your app to use the default app pool?. I've added the option for impersonation for completeness, but wouldn't recommend it.

pythonian29033 Over a year ago

sweet! I need that impersonation, I know it's not ideal for security, but these are just stats and I've been told to use impersonation regardless

pythonian29033 Over a year ago

Sweet! I found this line in my web.config file: <identity impersonate="false" userName="" password="" /> and changed it to: <identity impersonate="true" userName="MS\Benson" password="MyPassword" /> now it works!

Collectives™ on Stack Overflow

How to connect to MS SQL Server database from ASP using windows authentication specifying a windows user

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related