Re: Unable to use Kerberos Delegation from IE6 --> IIS 5.0 (asp.net)--> SQL 2000

From: Bob (BobxNoSpam)
Date: 03/24/03 

From: "Bob" <BobxNoSpam>
Date: Mon, 24 Mar 2003 14:28:21 -0600

WIA was not possible because the SQL Server's port # had been changed from
1433 to 1433,12345 (i.e. it now had 2 listening ports).

The web application was using an alias that forced port 12345 to be used.

No problem with this configuration except SQL Server isn't smart enough to
register all the ports in the Active Directory.

Thus, the only registered Service Principal Name (SPN) was for port 1433.

The solution was to manually register the additional port of 12345 and WIA
worked immediately.

PROBLEM:

========

When attempting to make a connection using ADO.NET connection string:

"Data Source=SQLBOX;Initial Catalog=PUBS;User Id=;Password=;Integrated
Security=SSPI;"

the following exception occurs:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

CAUSE:

======

SPNs not registered correctly to reflect SQL port change.

RESOLUTION:

===========

C:\>SETSPN -A MSSQLSvc/SQLBOX.company.com:11433 COMPANY\SA_SQL Registering
ServicePrincipalNames for CN=SA_SQL,CN=Users,DC=company,DC=com

MSSQLSvc/SQLBOX.company.com:12345

Updated object

C:\>SETSPN -L COMPANY\SA_SQL

Registered ServicePrincipalNames for CN=SA_SQL,CN=Users,DC=company,DC=com:

MSSQLSvc/SQLBOX.company.com:12345

MSSQLSvc/SQLBOX.company.com:1433

"Kevin McDonnell [MS]" <kevmc@online.microsoft.com> wrote in message
news:5gVZKaw7CHA.2536@cpmsftngxa06...
> Bob,
> Since you state that ASP works fine with Delegation, it leads me to
> believe that you have the proper permissions set. Have you enabled
> auditing for successfull and failed logins on SQL Server to verify that
> when it is working, you're getting connected using the proper account?
>
> I'm not sure why your IIS server is set to use Basic Authentication. If
> you're wanting to pass credentials of the client without having to specify
> a userid and password, the IIS security setting should be NT
> Challenge/Response.
>
> To troubleshoot further you may want to verify that you are getting a
> Kerberos authenticated connection from your browser to IIS by using the
> Kerbtray.exe utility. Also, you'll need to make network traces from IIS
to
> SQL to verify that we're able to use the SPN set for SQL Server.
>
> HTH
> Kevin McDonnell
> Microsoft
>
>
>

Relevant Pages

  • Re: advice about a worm intrusion alert
    ... Port 1434 is the SQL Browser service used for locating SQL Servers. ... connectivity - and I had no internet access on the wireless XP machine. ... Still no connection. ...
    (microsoft.public.sqlserver.security)
  • Re: Opening a SQLExpress database (Desktop) from PocketPC
    ... port it's running under ... > i can't even get a basic connection to my SQLExpress...!?!? ... > - using XP Pro desktop and can communicate to SQL Express via SQL Server ... >> When you specify the AttachDBFileName in the Compact Framework ...
    (microsoft.public.sqlserver.ce)
  • Re: Cannot connect to SQL express database ????
    ... Frist of all I forgot to open the port 1500 in my windows firewall. ... normally with SQL server. ... This connection string using sytax ... have made many connection like that with SQL server ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Express non-functional outside of VS 2005
    ... Not sure exactly what you mean by attaching from IIS. ... VS creates connections with the "User Instance" option. ... uses a user instance connection string, it will create an different SQL ...
    (microsoft.public.sqlserver.setup)
  • Re: connect to named instance w/ non-default port
    ... As Sylvain stated it might be using a different protocol and tcp may not be enabled on that server. ... You can use the existing connections event in profiler to see what the connection settings are for each connection or try the activity monitor. ... Andrew J. Kelly SQL MVP ... it sounds like there is no need to specify the port number in the connection string. ...
    (microsoft.public.sqlserver.connect)