current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 3 down vote favorite

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.

share|improve this question
up vote 4 down vote accepted

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.

share|improve this answer
    
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? :/ – ojek Feb 17 '13 at 17:56
up vote 0 down vote

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.
share|improve this answer
    
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? – ojek Feb 17 '13 at 16:28
    
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. – Darin Dimitrov Feb 17 '13 at 16:29
up vote 0 down vote

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.

share|improve this answer
    
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. – ojek Feb 17 '13 at 16:50
    
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. – Andomar Feb 17 '13 at 16:56
    
Yes, my webserver and sql server are on the same machine. – ojek Feb 17 '13 at 17:00
    
Heh i changed my app pool to run as my admin account, and it did not work, what is going on... – ojek Feb 17 '13 at 17:15
    
"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. – Andomar Feb 17 '13 at 17:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.