While fetching data from sql server in sharepoint web part getting error as Login failed for user 'NT AUTHORITY\IUSR'

Question

I am trying to get data from SQL server's table in SharePoint custom web part.There I am getting error as

"Login failed for user 'NT AUTHORITY\IUSR'".

here is my simple code

DataTable table = new DataTable();
string ConnectionString= "Data Source=PC11;Initial Catalog=Employee;Integrated Security = SSPI";
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                Console.WriteLine("connection created successfuly");
                string command = "SELECT * FROM Employees";
                using (var cmd = new SqlCommand(command, conn))
                {

                        Console.WriteLine("command created successfuly");
                        SqlDataAdapter adapt = new SqlDataAdapter(cmd);
                        conn.Open();
                        Console.WriteLine("connection opened successfuly");
                        adapt.Fill(table);
                        conn.Close();
                        Console.WriteLine("connection closed successfuly");

                }
            }

Answer 1

You should be aware of using Integrated Security = SSPI in your connection string will lead to using the Application pool account credentials of your SharePoint web application for authentication!

So you should

• Make sure that the Application pool account of your web application has sufficient permission to access the Employee database with trusted connection = true; in your connection string.

• Or use SQL authentication in your connection string by setting the username and password in your connection string without using Integrated Security = SSPI.

By the way, it's not recommended to set your connection string in code as you did, it should be defined as a key in your web config or use Secure Store

For more details check Where to store Your custom SQL Connection Strings in SharePoint

Answer 2

In my case the user was not allowed to login to the database. First check if the user has permissions on your database. To do so login to the database with the SQL Server Management Studio

1. Select your database
2. Expand the "Security" node
3. Expand the "Logins" node
4. Check if the user NT AUTHORITY\IUSR is listed here

If the user is not listed:

1. Right-Click on "Logins" and select "New Login"
2. Type in Login Name NT AUTHORITY\IUSR
3. Select "User Mapping" on the left pane
4. Check the database where the permissions are needed
5. Check the permissions that are needed (in my developer Environment I chose db_owner)
6. OK

It took me some times because I think I was not careful enough ;)