July 11, 2002 at 12:06 pm
Using integrated Windows authentication, how do I pass the users identity to the SQL server?
- All my users and servers are in the same domain.
- I wish to limit or eliminate login screens.
- Appropriate permissions are set up on the SQL server.
-- IIS directory security is "Integrated Windows authentication" only
-- Request.ServerVariables("LOGON_USER") returns the correct user name
- The ASP application works great if I embed SQL UID and password in the connection string
But I want data access and results controlled by each user's SQL permissions.
Without UID in the connection string, application returns this error:"
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."
Connection string = "Provider=SQLOLEDB;" _
& "Server=ServerName;" _
& "Database=DBName;" _
& "Trusted_Connection=Yes;"
I've combed through half a dozen books and was surprised to find no help. Most authors embed UID "sa" and password in the connection string. Yikes!
July 11, 2002 at 12:34 pm
What version of IIS?
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 11, 2002 at 12:46 pm
IIS = Microsoft-IIS/5.0
SQL server = Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
July 12, 2002 at 4:51 am
Are you running Active Directory? There is a feature of Kerberos called delegation of authentication which allows passing user credentials in a multi-tier environment.
Ryan
July 12, 2002 at 8:36 am
Not running Active Directory
New info: I changed the connection string and set IIS to Basic Authentication
It works, but the extra login screen and sending clear text passwords over the network are not ideal.
' Connection string
Str = "Provider=SQLOLEDB;" _
& "Data Source=ServerName;" _
& "Initial Catalog=DBName;" _
& "Trusted_Connection=Yes;" _
& "Integrated Security=SSPI;"
July 12, 2002 at 10:34 am
Most authors implement a worst practice to make things easier. A disservice to reads IMHO.
The SQL box will accept Windows Auth. Set up IIS for Basic and Windows auth, it should pass through credentials with no extra box.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply