Windows Authentication with local windows account
-
I'm trying to move my classic ASP web application to windows authentication instead of using a SQL Server login, because I don't want to pass the user id and password in my connection string.
The connection string below will connect to my DB on SQL Server 2005 but certain pages that query the DB take forever to load:
"Provider=SQLOLEDB.1;Data Source=xxx.xxx.x.xx;Initial Catalog=xxx;Integrated Security=
But when I use the SQL Server Login, like the one below, those pages load fine:
Provider=SQLOLEDB;Data Source= xxx.xxx.x.xx;Initial Catalog=xxx;User ID=xxxxxx;Password=xxxxxxx;Connect Timeout=15;Network Library=dbmssocn;"
Also, I'm not running IIS using a domain account, because my web servers site outside of the domain. So I set up identical local user accounts on the web servers and on the SQL Server. And I created a SQL Server login for the local account and gave it the database role db_owner.
Could those certain pages be taken so long to load because there is something wrong with my connection string or is it a problem with using the identical local user accounts for the windows authentication?
I would appreciate any help! Thanks in advance.
-
Your accounts probably aren't going to work from one machine to the other since they aren't in the same domain (and using domain accounts) The accounts are unique on each machine, even though they use the same name. Think of it more like SERVERNAME\USERNAME, and that's more like what you're dealing with.
Hope it helps!
-
Yeah, that is what I thought at first but I read that it is possible to use matching local accounts on IIS and the SQL server. This is stated in this article: http://www.15seconds.com/issue/020715.htm.
The weird thing is that I can connect to SQL Server but it takes a lot longer to load the pages if I use the Integrated Security instead of a SQL Server Login to connect.
Thank you for your reply.
-
Bryan -
Some of my work is in a Domainless World, and we have a number of external web applications (outside our Domain) that access our SQL Server 2000 instance using Integrated Security, where the same User/PW exists in the calling Domain and local on the SQL box.
I have never had complaints of slow web client connects to the SQL Server. We ran on Windows 2000 and recently migrated to Windows Server 2003, but we have only 20 or so Windows accounts. To move that SQL instance to a new machine and rectify the SIDs requires some fancy footwork.
The slow connects might be due to local password LAN/HASHING on the local server. There is a default Windows Security Policy that Defaults to keeping 10 I believe. Is that Policy turned off? You might get a network guy to help you look at that possibility. I would suggest turning on the Windows Security Audit Policies on the SQL Server box to help you see what the authentication footprint to the server looks like. That should tell you some things. A Security/Profiler trace on the SQL instance could also be helpful.
We recently deployed a APS web app that sits outside the domain and used the .NET Data Protector to encrypt our Data connection strings that use SQL Authentication (Google DPAPI). I know we are using IIS 6.0 and .NET 2.0. I'm not a programmer so I don't understand the nuances but using the machine based DataProtector.exe generates a very strong encryption key. There may be some overhead using it on a super-busy website with many connections but for our small offerings it's teriffic.
Hope this is helpful.
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
-
Thank you for your response Stuart. Let me look into the Windows Security Policy on the SQL Server box to see how those settings are configured.
-
Security Options/Interactive Logon: Number of previous logons to cache (in case domain controller is not available).
When going LIVE with a domainless standalone SQL Server, one Local Windows account User/SID was missed in a DB due to user name mismatch. After correcting the SID mismatch in SQL Server, it took maybe 15 minutes for SQL to get the handshake with Windows to allow that Login to be recognized. My best guess is that the cache was eventually flushed.
There may be other Policies affecting this issue. I have found that since much of the Windows World assumes Domain, information about running in a standalone world can be scant.
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply