SQL Database not connecting after deploying to server. Integrated Security

While designing applications with Stadium, using a database is a key component to a lot of sites. The user normally sets up a connection in the Stadium Designer, and it works. However, once they deploy it to their server, the SQL Connection stops working and returns an error, commenting that the local machine’s user does not have access to the Database.

Why does this happen?

The connection string is set up with “Integrated Security”, which means the user that is logged in to the machine’s username and password is used to access the database. By default on SQL installations, the normal user will have access, and the connection will be successful.

However, once the application is deployed to the server, the application is now being run in the background (you don’t have to be logged into the server with your default username and password in order for a web application to work.) In order for Windows (IIS) to still run your application, even without you being logged in, the server uses a machine user to run web applications.

This machine user is not set up as a valid login for your SQL database. Thus, while using integrated security on the connection string, this user will not be allowed to access your SQL.

There are 3 ways to fix this:

  1. Change your connection string to include an SQL username and password which you have created on your SQL server. It is not good practice to use the built in “sa” account. (If you’ve installed SQL server on your PC yourself, make sure you enable SQL authentication, and not just Windows authentication.)

  2. Add the specific Machine User as a valid user to your SQL database. (just google for your type of SQL Server, there are a lot of examples)

  3. Run your Stadium instance as your normal user. For this you’ll need to go into your Services and edit the StadiumServer Service to “Run-as” your normal user.