Tuesday, January 22, 2013

Security settings for your Web API service hosted in IIS to access SQL Server

If you experience "Login failed for user 'NT AUTHORITY\\SYSTEM'" exception when your code is trying to connect to SQL Server from a Web API service, just read and perform this procedure. It worked for me and hopefully work for you too!

First of let's assume your web application is called MyApp.

Step 1 - In IIS create a new Application Pool for your web application named as MyAppPool and make sure your application uses the Application Pool you create.

Step 2 - Open Advanced Settings for your newly created Application Pool and make sure Identity property has a value equal to ApplicationPoolIdentity

Step 3 - In your SQL Server Management Studio, right click on Security->Logins and then select "New Login"

Step 4 - For "Login Name" type in "IIS APPPOOL\MyAppPool" and select your database as the default dayabase. Do not search for the user because it doesn't shown in search result. Just type in the name.

Step 5 - Now you need to add the use to your database. Under database name expand the tree and right click on Security->User->New User.

Step 6 - For user type select "SQL User with login"

Step 7 - Copy and paste "IIS APPPOOL\MyAppPool" (the newly created login name) for "user name" and "login name" fields and also select a default schema as dbo.

Step 8 - Finally right click on database and select properties and for the permissions select appropriate permissions based on your application requirements.

No comments:

Post a Comment