I am connecting to a SQL Server database via LINQ in my ASP.NET application (returning results to a web service). The problem is that it only returns results when I'm running it in debug/test mode - When I publish the service I get an error telling me "Login failed for user 'NT AUTHORITY\NETWORK SERVICE' "
So how do I set / provide the credentials I want the site to use to log into SQL Server?
Thanks in advance for any help
You have to set the credentials in the connection string.
It's possible that the connection string to use on the server will be different than the credentials to use during Development. Ask your DBA which credentials you should use.
You are likely not passing a connection string into your DataContext constructor, causing your program to use the connection string in the constructor, which you have been using for development.
So instead of using something like this:
using (var dc = new MyDataContext()) {
...
}
use
using (var dc = new MyDataContext(MyConnectionString)){
...
}
... and take MyConnectionString from your config file...
A few questions first
I'm going to assume you're using a full version of SQL, because if you're using SQL Express user instances will not give you this problem.
If it's on the same machine then you need to add Network Service to the list of allowed users in SQL. To do this start SQL Management studio, expand the security folder, then right click on Logins and choose New Login. Click search on the "Login - New" dialog, making sure Windows Authentication is selected then enter Network Service in the object name box and click Ok. Choose the database you want it to access from the drop down list in the Login - new page and click ok. Finally expand out the databases folder and for each database you wish to grant access to expand out security, right click on users, then select the network service login name, give it a name in the dialog of Network Service and grant it the right access by checking the role in the Database role membership list. If you're messing around db_owner will be fine, you will want to lock this down later.
If the SQL server is on a different box and you are in a domain you can run the application pool as a domain user and grant that access on the SQL box. How to do this varies on the version of IIS.
Finally you can use SQL logins and passwords if the SQL server is configured to do this. Create a SQL user and then add them to the connection string like so
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
In your question, it is unclear if you are using a different database between development and production. Typically, you would have (at least) two copies of the database and would set the connection string to point to the correct environment when you deploy typically by changing the value in the web config. Alternatively, you could set the connection string at runtime using the technique Dave mentions.
In your case, I suspect that you are using integrated authentication and getting cought off-guard by SQL security permissions. When you are testing this in your local environment by debugging it in Visual Studio, it runs under your security credential. Most likely, your user account is an administrator (db owner) in the database and thus you have full permission against the database.
When you deploy this, IIS is running under the NetworkService credential. When you try to access the database, the request fails because NetworkService has not been given rights in the database on each of the tables/views/sprocs/etc.
If you have mixed mode set on your server, you can set the permissions on each database object to a SQL user and set the SQL user and password in your web.config. Alternatively, you can change the user account that the IIS process works under and configure the database to work with that user.
