CODEWITHSUNDEEP
asp.netsql-serverasp.net-mvcwindows-server-2008connection-string
ojek
ojek

Reputation: 10068

How to configure SQL Server to work with ASP.NET MVC with windows authentication?

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.

Upvotes: 3

Views: 5299

Answers (3)

shakib
shakib

Reputation: 5469

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.

Upvotes: 6

Andomar
Andomar

Reputation: 238078

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.

Upvotes: 0

Darin Dimitrov
Darin Dimitrov

Reputation: 1038720

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.

Upvotes: 0

Related Questions