Database problem moving from VS Development Server to IIS7

Question

I have a project I am trying to move to run on IIS7 instead of the Visual Studio Development Server. Everything works on VS Development Server but when moving to IIS7 its seems to not be able to read anything from the database, but doesn't give a database error, instead it gives a NullReferenceException on the information I'm trying to get from the database. The project uses Linq to get information from the DB if that matters. Also, here is my connection string. I have tried messing with it to no avail.

     <connectionStrings>
       <add name="MyProjectConnectionString" connectionString="DataSource=MY-PC\SQLEXPRESS;Initial Catalog=MyProjectsDB;Integrated Security=True" providerName="System.Data.SqlClient"/>
     </connectionStrings>

System Information: IIS7 running on Vista Business, SQL Server 2008 Express, and it works fine with Visual Studio Development Server and SQL Server 2008. Thanks for any input!

Answer 1

Make sure the user IIS is running as has permission to connect to the DB. Check the Application event log to see if there are any 'unable to connect' events. I suspect your NullReferenceException is actually an attempt to use a failed SQL connection.

Answer 2

It's a security issue. You can try using a SQL server user/password or give the IIS user access to that database. The IIS user probably is NETWORK SERVICE

Answer 3

Change the data source so that it points to the server, not your own computer, or simply use ".\SQLEXPRESS" as data source.

To use integrated security, you may need to add the network account as a user in the databse. Alternatively you can use SQL Server security instead, specifying a user name and password for a user that you set up in the database. At connectionstrings.com you can find examples on how to write different connection strings.

The fact that you are getting a null reference exception suggests that you are catching the exception in your code and just ignoring it. If you do, that is a very bad practice. Errors may occur without you even noticing, and you don't get any clue on what's wrong if you notice the error.

Answer 4

You can do one of these:

1. Change the App Pool identity to a user that has SQL rights
2. Change to SQL authentication (username + password)
3. Add the domain\webserver$ machine account to SQL eg BOBSGARAGE\WEBSERVER1$
4. Enable pass through authentication so the credentials are passed from browser to SQL Server
5. Host SQL and IIS on the same server (bad idea)

Answer 5

From your connection string "connectionString="DataSource=MY-PC\SQLEXPRESS;Initial Catalog=MyProjectsDB;Integrated Security=True" it looks like you're going to have trouble with the integrated Security setting. When you're moving a database from local to some other place the aspnet user will usually not have direct access to the database as it would on a local machine.

On the server you're moving to, make sure that you have mixed mode set on the SQL Server itself, create a user and password on SQL Server for the database that you're attempting to connect to and make sure that they have the apporiate levels of persmissions to add, edit, delete, etc.

Now go back to your web.config's connection string and change the connection string to have the following: connectionString="Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;".

Good luck with your project.