3

This is my connection string:

<add name="MyDb" providerName="System.Data.SqlClient" connectionString="Server=localhost;Data Source=.\MSSQLSERVER;Initial Catalog=MyDb;Integrated security=SSPI;" />

It works just fine on my localhost machine, but on my server, after deploying my application, it doesn't want to work, it gives me this error:

[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)]

When i changed connection string to use account credentials - sa, it connected just fine, so i know exactly that this is problem of bad sql server configuration.

So my question is: I have freshly installed sql server instance, how do i configure it so it allows windows authentication for my asp.net mvc application?

Edit: Well, after all, i looked at my connection string once again, and removed Data Source=.\MSSQLSERVER. It helped, everything works fine now, thank you guys for helping.

Improve this question

3 Answers 3

Reset to default
6

If your application is running under DefaultAppPool of IIS, it uses the "IIS APPPOOL\DefaultAppPool" acount to access db in integrated mode. So to grant the account in SqlServer:

  1. open sqlserver instance
  2. expand security, select "New Login"
  3. set "IIS APPPOOL\DefaultAppPool" for login name, give db access for the account from "User Mapping" tab.
  4. click ok to create.

hope this helps.

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

1 Comment

Well it's something, thanks. Anyways, i did as you said, and there was user called IIS APPPOOL\DefaultAppPool. I also changed my application to run from defaultapppool (btw defaultapppool is listed as local service). But i still get the same error. What can I do now? :/
0

Take a look at the following article on MSDN which illustrates how you could change the authentication mode for SQL Server.

You could SQL Server management studio:

  1. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
  2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
  3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
  4. In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
Improve this answer

2 Comments

Yes, i have enabled windows auth in there. I guess my problem is, because i did not add any login? Do i need to add to my logins network service or something like that?
Yes, you need to add an account which is associated with the database you need to access. Then you should have the application pool of your web application execute under the same account. Server related questions are better suited to serverfault.com so if you need some more details don't hesitate to post your question there. StackOverflow is a programming related question, no server configuration issues here.
0

First figure out under which Windows account your web page is running. You could for example print the value of:

User.Identity.Name

If it's a local-only account like "NETWORK SERVICE", you've got to change the account. If you're running in IIS the account is a property of the application pool.

Once you know the account, you can grant that account login rights in SQL Server.

Improve this answer

5 Comments

App pool is set for NetworkService. I added login to my sql server called network service (it's listed as NT AUTHORITY\NETWORK SERVICE) and granted it admin rights. Still no results.
Each server has its own "NETWORK SERVICE". So that only works if the SQL Server is on the same server as the Web server. Try to configure a different (or new) account.
Yes, my webserver and sql server are on the same machine.
Heh i changed my app pool to run as my admin account, and it did not work, what is going on...
"NETWORK SERVICE" is a special account that may have special things that don't work. I've never seen it used to access SQL Server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.