0

I have read and tried all the suggestions from [https://stackoverflow.com/questions/22415319/system-data-sqlclient-sqlexception-login-failed-for-user][3]

But none of those suggestions work. Thank you very much if you know why my setup have this error.

From SQL Server Studio Management, I am able to login with no error to SQL Server either locally through Windows Authentication or through a user account from another computer on the network. Both of my SQL server and IIS web server are residing on the same machine.

System Setups:
Server Operating System: Windows 7
SQL Server: SQL Server Developer version 2012: Run under Window's Administrator account
Web Server: IIS 7.5: Run under Window's Administrator account
SQL Server Authentication Mode: SQL Server and Windows Authentication Mode
IIS Server Application Pool Setting:
-Managed Pipeline Mode: Integrated Process
-Model Identity: Network Services

web.config Setting:
Connection string and system.web:

<add name="DBCS" connectionString="server=localhost;database=WebServer;integrated security=SSPI;persist security info=True"/>
<authentication mode="Windows"/>

Here is how I make connection to the SQL Server: 

string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; 
using (SqlConnection Conn = new SqlConnection(connectionString)) 
    { 
        SqlCommand Cmd = new SqlCommand(queryString, Conn); 
        Cmd.Connection.Open(); 
        Conn.Open(); 
        Cmd.ExecuteNonQuery(); 
        Cmd.Connection.Close(); 
        Conn.Close(); 
    }

Here is the error I got from the Chrome web browser:
Source Error:

Line 92: {
Line 93: SqlCommand Cmd = new SqlCommand(queryString, Conn);
Line 94: Cmd.Connection.Open(); <---- ERROR 
Line 95: Conn.Open(); 
Line 96: Cmd.ExecuteNonQuery();

Stack Trace:

[SqlException (0x80131904): Login failed for user 'HJHHOME\HENRYJHU-PC$'.]
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +345
   System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +156
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +258
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +312
   System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +202
   System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +413
   System.Data.SqlClient.SqlConnection.Open() +128
   _Default.DBUpdate(String queryString) in c:\inetpub\henrywebroot\Default.aspx.cs:94
   _Default.Page_Load(Object sender, EventArgs e) in c:\inetpub\henrywebroot\Default.aspx.cs:49
   System.Web.UI.Control.OnLoad(EventArgs e) +106
   System.Web.UI.Control.LoadRecursive() +68
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3785

My Application Pool Setting:
Screenshot
Screenshot

Version of .Net Framework running on IIS Server:
Screenshot
Screenshot

Kuldeep J
  • 382
  • 5
  • 12
No Name
  • 21
  • 3
  • What user is your IIS application pool running as? – mason Jul 02 '19 at 18:26
  • `integrated security=SSPI;Trusted_Connection=False` is contradictory. Use `Integrated Secutiy=SSPI` *or* `Trusted_Connection=Yes`, but do not combine these settings, and certainly not with contradicting values! – Jeroen Mostert Jul 02 '19 at 21:50
  • Yea, being able to login with SSMS only proves your account has access rights but not the ISS (IUSR) user. – wp78de Jul 02 '19 at 22:01
  • Jeroen Mostert: I just removed Trusted_Connection from the connection string and re-test. But I still got the exact same error. – No Name Jul 02 '19 at 22:06
  • mason: I am running my IIS application with the Window's Administrator account. – No Name Jul 02 '19 at 22:09
  • wp78de: How can I configure my IIS server to have access to local sql server? – No Name Jul 02 '19 at 22:12
  • No, the error you got is different now: it includes the actual user name (demonstrating that it's using Windows authentication, not SQL Server authentication). The IIS app pool is evidently configured to authenticate using the machine account (`domain\computername$`). – Jeroen Mostert Jul 02 '19 at 22:22
  • Jeroen Mostert: I deleted the old Application Pool for .Net 2.0 and created a new one for .Net 4.0. Also, I modified my web.config file so it would be compatible with .Net 4.0. The error seem to be the same, but now it is triggered by a different method inside my .cs file. So it looks like my modification of both the Application Pool and web.config file did not help. Do you have any idea to what is going on? – No Name Jul 03 '19 at 00:18
  • Jeroen Mostert: I just loaded a screenshot of my application pool setting. Could you tell me what is wrong with my setting? Thank you. – No Name Jul 03 '19 at 00:25
  • I would suggest you to replace this `` Let know know whether it is working. – Md Farid Uddin Kiron Jul 03 '19 at 01:54
  • Also can try with `string connectionString = ConfigurationManager.ConnectionStrings["Data Source=.;Initial Catalog=WebServer;Connection Timeout=30;Integrated Security=True;"].ConnectionString;` – Md Farid Uddin Kiron Jul 03 '19 at 01:55
  • If it still does not solve just let me know there are many way out there. – Md Farid Uddin Kiron Jul 03 '19 at 02:00
  • Thank you to Peja from the SQLServer forum. He explained it so well that it took me less than 15 minutes to completely fixed everything. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/aad95f4a-350b-4175-9240-d81ea3f7fc13/how-to-create-windows-authentication-user-in-sql-server-after-installing-sql-server-2008?forum=sqldatabaseengine – No Name Jul 03 '19 at 03:09
  • I will suggest you try using Sql Authentication. So: – Cujoey Jul 03 '19 at 07:44

0 Answers0