SQL Server error in IIS asp.net web page

Question

Good day everyone! I've been having a problem, and I figure it out that I'm not the only one with this 'kind' of SQL Exception. But, I done everything that I found and with the same result. So, please, let me introduce my problem.

I've been working in asp.net web application. Developed in Visual Studio 2015 (community version). In my project I have some SQL Connection to my local database (in the same computer that my project). Everything is going well when I click "Debug" (with my favorite browser). I can make querys (select, inset, update, etc). Now I want to make it public in my intranet (my cowerkers want to wwork with this app as well). So, I moved my project folder to

C:\inetpub\wwwroot

And yes, the IIS web site is configured (correctly, I think, well I have another intranet apps working fine!)

The Default page in my web page is a Login.aspx and when I try to access with the correct keys (username and password), SQL shows me an error.

Invalid login attempt. Database issue.System.Data.SqlClient.SqlException (0x80131904): Error de inicio de sesión del usuario 'IIS APPPOOL\SADI'. en System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) en System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) en System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) en System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) en System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) en System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) en System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) en System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) en System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) en System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) en System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) en System.Data.SqlClient.SqlConnection.Open() en SADMIN.Login.btnLog_Click(Object sender, EventArgs e) en C:\Users\MyUser\Documents\Visual Studio 2015\Projects\MyProject\MyProject\Login.aspx.cs:line 43 ClientConnectionId:6cc4ef30-4720-4c45-ba87-d85f39804685 Error Number:18456,State:1,Class:14 Enviar

And if your're wondering... This is my line 43:

conn.Open();

And this my BackCode in the Login.aspx

  try
                 {

                     SqlConnection conn = new SqlConnection(@"Integrated Security=SSPI;Persist Security
 Info=False;User ID=sa;Initial Catalog=MyDatabase;Data
 Source=PCUsername\SQLInstance");
                     SqlCommand cmd = new SqlCommand();
                     SqlDataReader reader;

                     cmd.CommandText = "spcLoginValidate";
                    cmd.CommandType = CommandType.StoredProcedure;
                     cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = txtusername.Text;
                     cmd.Parameters.Add("@passw", SqlDbType.VarChar).Value = txtpassword.Text;

                     cmd.Connection = conn;
                     conn.Open();

                     reader = cmd.ExecuteReader();
                     while (reader.Read())
                     {
                         idRole = Convert.ToInt32(reader[0].ToString());
                         Session["User"] = reader[1].ToString();
                         Session["ID"] = idRole;

                     }



                     reader.Close();
                     conn.Close();

                     lblError.Visible = true;
                 }
                 catch (Exception ex)
                 {
                     lblError.Text = "Invalid login attempt. Database issue." + ex;
                     lblError.Visible = true;

                 }

In the Catch section I have the labelError whith the SQL Exception, and in that label is where my web page shows me the error that I gave you before.

My SQL Server services are running, so I don´t know what to do! Any help from you guys it would be awesome! I've been trying to resolve this issue for 3 days, and still have it.

Moho · Accepted Answer · 2016-04-01 16:51:04Z

4

You need to grant your IIS AppPool\SADI account access to the DB or disable integrated security and add a password for the sa user account.

Creating SQL Server Logins and DB Users: https://technet.microsoft.com/en-us/library/aa337545(v=sql.110).aspx

edited Apr 1, 2016 at 16:51

answered Apr 1, 2016 at 16:24

Moho

16.8k1 gold badge36 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Moises Gonzaga Over a year ago

How can I do that?

Moho Over a year ago

added a link to a technet article

Moises Gonzaga Over a year ago

well. Sql server login and db user done it... but how cain I grant the accoount access (IIS AppPool\SADI). Do you have any ideas?

Steve B · Accepted Answer · 2016-04-01 16:24:36Z

1

The error is self explaining. Your application pool account does not have sufficient rights on the SQL database.

You may:

change the identity of your application to a domain account, and grant privileges to this account on the DB
change SQL authentication to user/password and create a sql login (not recommended though)

Also please check if user impersonation is disabled in the web.config file (this may lead to such issue if not)

answered Apr 1, 2016 at 16:24

Steve B

38k24 gold badges109 silver badges186 bronze badges

2 Comments

Moises Gonzaga Over a year ago

I did the number 2. But still the same issue. And

Steve B Over a year ago

as @Moho stated, your connexion string is still setup to use integrated security. User id and password won't be used.

Moises Gonzaga · Accepted Answer · 2016-04-02 05:04:29Z

1

Well, after 4 days searching for information about this issue. And still don't know the reason.

I found what I need, after all, this resolved my problem. I just ran this sentence in SQL Server's query (inside my database)

 exec sp_grantlogin 'IIS APPPOOL\DefaultAppPool'  
 use yourDB exec
 sp_grantdbaccess 'IIS APPPOOL\DefaultAppPool'

Special thanks to Moho and Steve B, your answers were fundamental to found the resolution.

edited Apr 2, 2016 at 5:04

answered Apr 1, 2016 at 22:25

Moises Gonzaga

1593 silver badges17 bronze badges

Collectives™ on Stack Overflow

SQL Server error in IIS asp.net web page

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related