I CAN connect to SQL from IIS in all ways but one!

IIS box to remote SQL Server box (Member Server on Domain)

This doesn't work when IIS Iusr is created on the "Domain" then added to the
member server's SQL Server! Isn't there some issue with Kerberos not
passing the token to the member server?

The above works when I connect to SQL Server when it's running on the DC, or
when I add a "Local" IUSR to the "member server". But I would like to use a
Domain user on the Member Server SQL Server.

TIA,
Dan Foxley

Hi Dan,

My name is Michael and I would like to thank you for using Microsoft
newsgroup.

According to your description, I am not quite clear what the problem is on
your side. I would like you to provide more information so that I can
narrow down this issue.

1. What the version of SQL Server (Service Pack) which you cannot connect
to?

2. What do you mean that you connect to SQL Server from IIS? Do you mean
that you connect to SQL Server using ADO or ADO.net?

3. You wrote "I CAN connect to SQL from IIS in all ways but one!", do you
mean that you can connect to SQL Server using Query Analyzer and SQL Server
Enterprise Manager (SQLEM) with the same domain account?

4. Please provide the detailed error message.

Also, such issues tend to be complex and take up extensive research time.
I'd like to set your expectations that it may take a while for us to help
you narrow down the problem and we may eventually redirect you to PSS to
continue working with a dedicated Support Professional. If this is
critical, I'd recommend contacting PSS and opening a Support incident
troubleshoot this further. If you need any help in this regard, please let
me know.

I am standing by for your response.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Yuan,
See inline below...and notes from the referenced KB.
I've used the KB ass ref. 247931.

=============================

a.. If users are allowed anonymous access, verify the following:
a.. If the user is configured as the Anonymous user in IIS, they must
also be configured in a Windows NT account on the computer running SQL
Server.
b.. If SQL Server and IIS are not on the same computer, create the user
as either a domain account accessible to both computers or locally on both
the SQL Server computer and the IIS computer with the same password. If you
create the user locally on both computers, the user must be given the Log on
Locally right on the SQL Server computer. If the user is a domain account,
the user must be given "Access this computer from the network" right on the
SQL Server computer.
================================


[quoted text, click to view]
SQL Server2k SP3 on Win2k SP4

[quoted text, click to view]

We connect with the following connection string:
session("ConnectionString") = "Provider=sqloledb;Network
Library=DBMSSOCN;Data Source=IPADDRESS;Initial Catalog=DATABASE;Integrated
Security=SSPI;"

[quoted text, click to view]

I was referring to using a Local or Domain "Synchronized" account to connect
to SQL from IIS. When I connect to SQL hosted on a DC from IIS (not on the
domain) I'm able to connect. When I access SQL on a member server of the
same domain, from the IIS server (not on the domain) I'm NOT able to
connect. What I see happening is that the Domain IUSR that I create on the
2 SQL servers (one on the DC and one on the Member Server) in User Manager &
in SQL can only authenticate on the SQL Server hosted on the Domain
Controller. To get IIS to authenticate on the SQL Server that is the Member
Server, I must create a Local User--I cannot use the same Domain IUSR! Is
this a Kerberos issue?

I hope this is clearer..


[quoted text, click to view]
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."

[quoted text, click to view]

Hi Dan,

Thanks for your feedback. Based on my research, this article 247931does not
apply to Microsoft Windows 2000 Active Directory domains. The Microsoft
Windows NT authentication model that is discussed in this article only
applies to Windows NT domains." Are you using Win 2K AD domain or Win NT
domain?

I would like you to provide the following information so that I can perform
further research.

1. What the version of SQL Server (Service Pack) on your side?

2. When SQL Server is running on the DC, are you using a domain account?

3. Does the problem only happen when you are trying to connect to SQL
Server (MachineA) via IIS (MachineB) from another client machine (MachineC)
and SQL server and IIS Server are on separate machines and when using NT
authentication.

4. I would like you to provide the detailed information when you connect to
SQL Server. Which provide you used to connect to SQL Server, ODBC or OLE
DB? Do you mean that you connect to SQL Server using ADO? If so, please
provide the code statements used to build the connection.

Please make sure the following tips have been done on your side.

1. To enable Windows NT authentication, through the Internet Information
Server (IIS) computer, Basic authentication must be enabled for the Web
application.

2. Since you are creating a domain user account, make sure that the domain
user has been given "Access this computer from the network" right on the
SQL Server computer.

3. Since IIS and SQL are not on the same box, ensure that they've done the
following:

a. Start the Internet Services Manager (on the Directory Security property
page for the Web application).
b. Open the Anonymous User Account dialog box.
c. Disable the Enable Automatic Password Synchronization option, and then
manually enter the password for the domain user account.

Does the error occur again after confirming the above steps? If so, please
provide the detailed error message. I also would like you to provide the
exact steps that you have take the results in the error message.

1. Please check Event logs for the SQL Server machine to see if there are
any entries which would give more information.

2. Please set the audit level to all and check the Error log file to see if
there are useful information recorded in it after the error occur. (Right
click the server name in SQL Server Enterprise Manger-->Select Property
command -->Select Security Tab-->Choose All under the Audit lever option.)

For more information, please refer to the article on SQL Server Books
Online:
Topic: "How to set up Windows Authentication Mode security (Enterprise
Manager)"

Please try to connect to SQL Server SQL Server authentication. Because SQL
Server needs to be running in the Mixed mode of authentication. Please set
the related authentication for the SQL Server. (Right click the server name
in SQL Server Enterprise Manger-->Select Property command-->Select Security
Tab-->Choose "SQL Server and Windows" under the Authentication option.)

In addition, I highly recommend you look though the following articles, it
should help this issue a lot.
326985 HOW TO: Troubleshoot Kerberos-Related Issues in IIS
http://support.microsoft.com/?id=326985
319723 INF: SQL Server 2000 Kerberos Support Including SQL Server Virtual
http://support.microsoft.com/?id=319723
PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"
http://support.microsoft.com/?id=307002

I am standing by for your response.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

See In line..
[quoted text, click to view]
SQL2k SP3

[quoted text, click to view]
In the Development environment it is running as a Domain Admin (Development
Only)


[quoted text, click to view]
******************
Yes, But I have SQL A1 & A2 (A1 is on a DC & A2 is a Member Server in the
Domain) I'm testing failover to the second SQL Server A2 when this issue
happens. Everything is fine on SQL A1, I have the same settings on SQL A2,
but the ONLY way for IIS to Authenticate is for me to create LOCAL users on
SQL A2, it will not authenticate the Domain Users!
******************
[quoted text, click to view]
In the global.asa we use the following connection string...is this enough?
session("ConnectionString") = "Provider=sqloledb;Network
Library=DBMSSOCN;Data Source=IPADDRESS;Initial Catalog=DATABASE;Integrated
Security=SSPI;"


[quoted text, click to view]
No I'm using Anonymous connection and it works...for SQL Server A.

[quoted text, click to view]
Yes

[quoted text, click to view]


[quoted text, click to view]
Yes, I've had to change that and verified it with Adsutil.vbs


[quoted text, click to view]
No errors are generated...

[quoted text, click to view]

OK I will do this and see what shows..
[quoted text, click to view]

Hi Dan,

Thanks for your update. According to your feedback, I understand that there
are two servers installed SQL Server (A1 is on a DC & A2 is on a member
server). The version of all SQL Server instance is SQL Server 2000 SP3.
Everything is fine on SQL A1 using Windows Authentication (domain account)
for anonymous access. However, when you tried connecting to SQL Server (A2)
via ASP using Windows Authentication (domain account) for anonymous access,
"Login failed for user '(null)'" error occurred. It works using Windows
Authentication (local account Iuser). The IIS you installed and used is on
another server (not A1 or A2). The all servers are in the same domain. If I
have any misunderstood, please feel free to let me know, it is very
important for us to narrow down this issue.

I would like you to provide the more information so that I can perform
further research on my side.
1. This error message "Login failed for user '(null)'" is not enough. I
would like you to provide the completely error message.

2. According to your description in your previous, the problematic domain
account is created in the SQL Server instance member server A2. If so, I
would like you connect to the SQL Server on A2 with the problematic domain
account using Query Analyzer. Does any error message occur? If so, please
provide the detailed error message.

Based on my experience, because of the same reason, it is not recommended
we use the domain account for anonymous access in IIS. To workaround, I
would like you to indicate the user and password in the connect string in
ASP so that you are able to use SQL Authentication to connect to SQL Server.

827422 INF: Troubleshooting Connectivity Issues With SQL Server 2000
http://support.microsoft.com/?id=827422

Thanks for using Microsoft newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.