November 5, 2008 at 10:06 am
Our SQL 2005 db is set up for Trusted Windows Authentication.
I am able to connect to it using ASP.NET:
<add name="ConnectionString" connectionString="Data Source=ServerName;Initial Catalog=mydatabase;Persist Security Info=True;Integrated Security=True"
providerName="System.Data.SqlClient" />
However, I am unable to connect to it using classic ASP:
When I try this:
Set myConn = Server.CreateObject("ADODB.Connection")
myConn.Open "driver=SQL Server;" & _
"Server=ServerName;" & _
"Database=myDatabase;" & _
"Trusted_Connection=yes;"
I get the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
When I try this:
Set myConn = Server.CreateObject("ADODB.Connection")
myConn.Open "Provider=SQLNCLI;" & _
"Server=ServerName;" & _
"Database=myDatabase;" & _
"Trusted_Connection=yes;"
I get the error:
ADODB.Connection error '800a0e7a'
Provider cannot be found. It may not be properly installed.
I hope that this is an appropriate question for this forum. Thank you in advance for your assistance.
November 5, 2008 at 10:54 am
Hi,
What version(s) of the OS are involved on the client-side?
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 5, 2008 at 11:13 am
Windows XP is on the client side.
This will be accessed through a web browser so I am not sure if the OS matters.
The server that SQL 2005 is on is running Windows 2003.
November 5, 2008 at 12:24 pm
Hi again,
I meant where the ASP code runs.
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 5, 2008 at 12:28 pm
The asp code runs on Windows 2003.
Thank you.
November 5, 2008 at 2:05 pm
Oh, I see. So then if you are on Win2K3 SP1 you are on the latest version of MDAC. I thought I might find something related to older versions of MDAC but drew a blank.
This is a little bit outside of my domain but from browsing around, I realized ASP is not at all supported anymore (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=483782&SiteID=17).
I gather this is not some code that exists in a production environment so the question is if you want to spend time on troubleshooting it? MDAC issues can be quite tricky to get through.
I am sorry I couldn't be of more help!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 5, 2008 at 2:15 pm
... and by this I don't mean it is not going to work; just that it is unsupported as fas as Microsoft Support is concerned. (described here http://support.microsoft.com/lifecycle/ if you want to spend time on THAT 😉 )
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 5, 2008 at 2:20 pm
It's not working because you're not enabling impersonation on the IIS web site side. The query is being passed under the credentials of the IIS server service, unless you enable impersonation (which allows it to use your credentials.
There are some security implications to that (which I unfortunately can't remember), so you will want to read up on it first.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2008 at 2:21 pm
Thank you for your input.
November 5, 2008 at 2:39 pm
Matt,
Sorry, I am not sure how to enable impersonation in IIS.
Thank you,
Norbert
November 5, 2008 at 3:18 pm
Here's the starting point on this. It ain't pretty, but it never used to be pretty in the old days either....
http://support.microsoft.com/kb/248187
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 5, 2008 at 3:24 pm
Matt - thank you. I will give this a shot.
November 5, 2008 at 5:50 pm
I feel compelled to add a warning to this. I sometimes just focus on the question at hand, not necessarily stepping back to mention the dangers involved.
Because this is actually a reason "dangerous" way to implement things, especially in classic ASP. (this is actually why there was no "built-in" way to do this). Here's the problem: this method gives your ASP site access to a full authenticated windows token, essentially allowing it to act as you (thus the term "impersonate"). Anyone planning on doing harm who gets a hold of this could technically use it to do ANYTING to ANY system. Things like - remotely connect into another server of yours (if you happen to be a network admin), add/remove users (DBO or network admin), delete stuff, change stuff, etc.... They don't need to "escalate privileges" on a restricted service account - they already have ALL of your privileges.
And the problem is - it's almost impossible to adequately secure classic ASP. It was hard before, and with better tools and better hardware - running pretty much anything secure/confidential on classic ASP (especially public facing) is essentially taking your life in your hands these days.
So - you really DON't want to use this method IF:
- you don't plan on running this behind a firewall, on an HTTPS: connection.
- you want to use cached credentials
- you are planning on having users with escalated privileges access these pages.
- your SQL Security is not top notch and very tight.
It's just too darn easy to exploit.....
Sorry - should have mentioned this earlier....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 6, 2008 at 10:21 am
Matt,
Thank you very much for the great explanation. I am leaning towards changing the SQL server to Mixed Mode where I can just use a SQL login/password to connect from the ASP page.
Norbert
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply