I am tasked with converting our ASP app from SQL Server authentication to Windows Integrated authentication.
Currently, the app works like this:
- An .asp logon screen is presented (not asp .net, just asp)
- The user enters their SQL Server 2000 userid and password, and clicks OK
- An instance of a Visual Basic 6 ActiveX component is created, the user-entered userid and password are passed to a function within the ActiveX component.
- The ActiveX component creates an instance of ADODB.Connection, and uses the following connection string (broken apart for easier reading):
Provider=SQLOLEDB;
Initial Catalog=<dbname>;
data source=<servername>;
user id=<passed userid>;
password=<passed password>
- After the connection is established, a user security table is read, using the passed user-entered userid.
Everything works great.
Now, I am attempting to convert from a user-entered password to integerated security. The logon screen is gone, and the connection string's "user id" and "password" fields have been replaced in the ActiveX with integrated security (note the last 3 fields). The ASP page no longer passes uid and pwd to the VB function.
Provider=SQLOLEDB;
Initial Catalog=<dbname>;
data source=<servername>;
integrated security=sspi;
persist security info=false;
trusted_connection=yes
When I run this on my local machine, everything works great. I have already set up an MS SQL Server ID to match my network ID (e.g. DOMAIN\ed), and I can use the MS SQL Server Profiler app to see that, yes indeed, I am logged onto the database using integrated security.
The problem happens when I move the app to the test environment. Everything works as it did on my local machine, except for the userid (and password)... instead of using *MY* Windows userid and password, it is now using the *IIS* userid and password.
In other words, on my local machine, IIS is running under *MY* Windows ID, so that is the ID used for integrated security by the ActiveX app. When put into the test environment, IIS is running under a different domain\userid, and *THAT* is the ID used for integrated security.
What I want is, obviously, for the ActiveX component to use the *CLIENT* credentials instead of the *IIS SERVER* credentials.
From what I've already gathered on the Web, I cannot use the Windows NT Challenge / Response because it does not support "double-hop impersonations", leaving my only option as Kerberos-based integrated security (???).
Since the options available seem to very much depend on your version of software, here's my vitals:
Windows 2000 Professional Clients
IE 5.0+ browsers only
Classic ASP (no .net)
Home-grown Active X Components, created in VB6
ADO / OLE DB (SQLOLEDB)
MS SQL Server 2000
IIS 5
Remember, the .asp script is simply creating an VB6-created ActiveX object, it is the ActiveX object that's establishing the database connection to MS SQL Server.
And, BTW, the IIS server and the MS SQL Server are on different physical boxes, but same domain.
This seems like such a simple problem, but I haven't found anywhere on the web that has a solution.
500 points to the person who can solve this riddle!
