Connecting to SQL Server Database with Windows Authentication?

Question

When a user tries to connect to a SQL Server Database through Windows Authentication in my ASP MVC 3 app, with the following connection string:

<add name="btnintranetContext" connectionString="Data Source=BTN-SERVER;Initial Catalog=Intranet;Integrated Security=SSPI;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>

They get the following error:

Cannot open database "Intranet" requested by the login. The login failed.
Login failed for user 'User'.

I understand what this means, it means that I haven't given that user access to the database, but I want user's to be able to access the database without having to give them all access to it. I've tried specifying a specific Database user in my connection string like so:

<add name="btnintranetContext" connectionString="Data Source=BTN-SERVER;Initial Catalog=Intranet;User Id=mylogin;Password=mypassword;MultipleActiveResultSets=True" providerName="System.Data.SqlClient"/>

But I get a new error:

Login failed for user 'mylogin'.
The provider did not return a ProviderManifestToken string.

But I know for definite that mylogin has access to the database because when I try to access the database with the windows authentication connection string, it works. But for security reasons, I don't want to give everyone that uses my app access to the database, thats why I tried specifying a user in the connection string.

Additional info from my web.config file:

<authentication mode="Windows" />
<identity impersonate="true" />
<trust level="Full"/>
<authorization>
      <deny users="?" />
      <allow users="*" />
</authorization>

Svarog · Accepted Answer · 2011-10-27 07:36:57Z

1

Set if it's at all possible, and than give the user running the IIS website(usually NETWORK SERVICE be default) access to the database.
If setting impersonate to false is not possible for some reason, you should use SQL authentication instead.

answered Oct 27, 2011 at 7:36

Svarog

2,21816 silver badges21 bronze badges

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

5 Comments

CallumVass Over a year ago

If I set impersonate to false, the whole app fails when on IIS. I've given Network Service access to the database and it still fails. How would I use SQL authentication? Change the database to use SQL authentication and then pass a SQL user in the connection string replacing my User Id=.. and Password=... with the SQL user?

Svarog Over a year ago

Yup. That's exactly what you have to do.

CallumVass Over a year ago

Ok I just created a SQL user and passed it's user id and password in the connection string but I still encounter the error: Login failed for user 'sqluser'. The provider did not return a ProviderManifestToken string.

Svarog Over a year ago

Any chance you are using Entity Framework? If so, are you using DB-first or code-first?

CallumVass Over a year ago

I am using EF yes, and originally I was using code-first before switching to an actual database once my app was completed. But nevermind I managed to fix it, I forgot to restart my server once I switched to SQL and Windows Authentication mode. Thanks for your help, it is appreciated!

Collectives™ on Stack Overflow

Connecting to SQL Server Database with Windows Authentication?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related