The issue i have is:
3 Servers: DC1, SQL1 and Web1
All have IIS and an replicated intranet site http:/server/intranet
All have 2 DSN connections set exactly the same except one using TCP one using named pipes)
DC1's web app will access a search on SQL1 using named pipes but not TCP
SQL1 as above
WEB1's web app will not access a search on SQL1 using any of the DSN connections.
Errors recieved
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][Named Pipes]Access denied.
SQL is in mixed secuirty mode.
I am unable to work out the above no matter how much I play with teh permissions.
IIS on all Servers use Windows Authentication.
The SQL database is set to allow all Domain Users access.
Please Help
Did you allow WEB1\IUSR_WEB1 in your SQL Server?|||I have tried this but when trying to add WEB1\IUSR_WEB1 it says "Error 15401: WIndows NT user or group 'WEB1\IUSR_WEB1' not found. Check the name again.
I would assume this is due to WEB1\IUSR_WEB1 being an local account on WEB1.
I know this al seems basic but this is the first time I have had this issue.
Would this be that I need to add WEB1\IUSR_WEB1 to a group account in the domain and then allow this account access.
I have also tried a DSNLESS connection with the SQL system account but still get access denied.
|||You cannot add local users of other computer to principals in SQL Server as SQL Server cannot trust the identity of the user crendetials. I assume that you are currently using anonymous authentication with the mentioned user above. If you want to use the actual connecting users to gain access to the database with their individual rights on the database, you will have to turn of anynomous authentication and enable Windows Integrated authentication on the Web server. (If the web server and the SQL Server are on different machines, you will additionallY have to setup delegation) If you just want to have the Web Server connect with its Service account to SQL Server you will have to let it run under a domain account which is trusted by SQL Server. All users accessing the database will then use this credentials to auithenitcate at the database. If you don′t want both of the solutions and don′t want to use WIndows authentication per se, you will have to change your query string to let the application on the Web Server connect with a common SQL Server user which is the used by every web application consuming user.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Thanks for the advice.
I have managed to get the database to connect using Jens K. Suessmeyer advice.
What has worked is:
DSN Connection
SQL_Database with TCP Connection
USER: sql_system_account, PASSWORD: xxxxxxxx
ASP page Connection string.
<%
'if request("submit") = "true" then
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "dsn=SQL_Database; uid=sql_system_account; pwd=xxxxxxxx"
objConn.Open
'end if
%>
Note: Both must be in place otherwise errors still occour.
I will still have to look into the actaul web connection users and delegation in sql for the databases.
I would assume the problem was due to the Web server being originally setup on the Domain Controller and the sql databases being moved to a new server (Server 2003) and differant pernissions being lost.
Active directory was on the same server as the web server so authentication was handled differantly once the intranet and sql were moved to application servers.
