1

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");

                }
            }
1
  • Make sure that the Application pool account of your SharePoint web application is granted to access the Employee database in SQL Server > DataBase > your Database > Security > Users – Mohamed El-Qassas MVP Oct 3 '17 at 11:55
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

0
0

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 ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.