I can not seem to get sql server to recognize my credentials.
Asp.net recognizes me when I login but when I execute a sql command I get a login failed message.
The IIS server and SQL server are on different machines.
There are other applications the IIS server which are able to authenticate to the sql server. I believe the Active Directory settings are correct. I am investigating what I am doing differently.
I must be missing something.
- I check the IIS settings
- The web config is set to impersonate.
Below is the relevant information. If anyone has any idea as to what I missed or am doing wrong I would appreciate some help.
IIS Settings:
- 'Integrated Windows authentication' is checked
- 'Enable anonymous access" is not checked
Web Config
<authentication mode="Windows"/>
<identity impersonate="true"/>
<authorization>
<deny users="?" />
</authorization>
Page_Load Code:
Dim winId As IIdentity = HttpContext.Current.User.Identity
TextBoxMessage.Text = winId.Name + Environment.NewLine
Dim cnn As SqlClient.SqlConnection
Try
Dim sql As String = "*****"
cnn = New SqlClient.SqlConnection("Data Source=*****;Initial Catalog=****;Integrated Security=True")
cnn.Open()
Dim cmd As New SqlClient.SqlCommand(sql, cnn)
cmd.ExecuteNonQuery()
cnn.Close()
Catch ex As Exception
TextBoxMessage.Text += ex.Message
cnn.Close()
End Try
Output:
Domain\UserName
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Best Answer
This is key = The IIS server and SQL server are on different machines. You're facing the classic double hop issue. Think of it in this way:
Thus once you enable delegation as mentioned by Remus, your SQL Server will trust the credentials your IIS server is presenting on your behalf.
In terms of security, under delegation, it would be wise to choose:
Trust this computer for delegation for specified services only | Use Kerberos only | and then underneath the "Services to which this account can present delegated credentials" make sure you explicitly set only the server/port you need.