mads
|
 posted on 13/12/11 at 09:17 PM |
|
|
ado connection string for IIS to SQL using windows authentication
Hey up all,
I am currently writing an asp application that will connect to a MS SQL database but having trouble working out how to write the connection string to
allow the connection between the two using windows authentication.
- IIS and SQL are running on two different boxes
- Cannot use SQL authentication, only windows authentication as SQL box is not mixed mode (IT dept wont allow it to be)
- Have been set up with an AD account on the SQL instance with access to the specific database to use
I have tried the following already:
1) Setting up an ODBC connection on the IIS server but I can only set up SQL auth which isn't allowed or winows authentication, but this uses my
network details which works fine when I test the connection on the IIS box but doesnt allow a connection when I run the application from another
machine through IE.
2) Used the following connection string: "Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=
Your_Database_Name;UserId=Your_Username;Password=Your_Password;" but this still appears to be a SQL authentication method according to the SQL
server error log.
Can anyone point me in the direction of what I need to do? I have admin access on IIS box and also have someone in IT department who can do anything I
need on SQL box (except change to mixed mode).
Thanking you all in advance.
mads
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
|
|
McLannahan
|
| posted on 14/12/11 at 07:20 AM |
|
|
Mads
What error is the ad authentication giving? Do you need to enter your username as a full string? "domain\username" and not just username?
If it tries to authenticate your username but doesn't know who to ask it would fail.
|
|
|
mads
|
| posted on 14/12/11 at 08:07 AM |
|
|
code:
Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'WHITS\drugloc'.
/Calculators/Connections/druglocator.asp, line 11
^^ thats the error I get when I try the site on the IIS box. On my local machine, I simply get a 500 - Internal Server Error.
I have tried both domain\username and username but get the same error.
When looking at the error log via Event Viewer on my local machine, this is the error code I get:
code:
The description for Event ID ( 18456 ) in Source ( MSSQL$WOCSSQL_INSTANCE ) cannot be found. The local computer may not have the necessary
registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this
description; see Help and Support for details. The following information is part of the event: drugloc, Reason: An attempt to login using SQL
authentication failed. Server is configured for Windows authentication only.,
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
Pdlewis
|
| posted on 14/12/11 at 10:32 AM |
|
|
as you are using trusted connections no usr/pass is required
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
you could try adding impersonation just to test if this doesnt work
which sould look like this in the webconfig
<identity impersonate="true" userName="domain\user" password="password" ></identity>
Build Photo Album
Updated 05/02/2009
|
|
|
mads
|
| posted on 14/12/11 at 10:47 AM |
|
|
I understand I am using a trusted connection, but if I dont put a username/password, how does the SQL server know that the data request from IIS box
is trusted?
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
Pdlewis
|
| posted on 14/12/11 at 10:49 AM |
|
|
this depends,
if you want it to use the context of the user accessing the system then you use windows authentication on the IIS site and AD takes care of the rest
using tokens
If you want to use a specific context add the Impersonation line I put in above.
Build Photo Album
Updated 05/02/2009
|
|
|
mads
|
| posted on 14/12/11 at 10:58 AM |
|
|
Just tried the line you mentioned (with the impersonation) and get the following error:
Invalid authorization specification
Can you elaborate o nthe tokens bit as I have a feeling I may not have set up everything on the IIS box so that the SQL box thinks that any user
accessing the website is OK to use the connection with the AD account aforementioned.
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
Pdlewis
|
| posted on 14/12/11 at 11:12 AM |
|
|
as long as they are both on the same domain you should be fine
what authenication are you using for the website?
windows/forms/none?
Build Photo Album
Updated 05/02/2009
|
|
|
TimEllershaw
|
| posted on 14/12/11 at 11:13 AM |
|
|
I seem to remember we had "fun" with this in the past. I'll look back and see if I can work out what I did.
I seem to remember having to set "Integrated Security" to True(?) in various places ( connection string and web.config ?)
In the mean time, this may help :
http://msdn.microsoft.com/en-us/library/bsz5788z.aspx
http://www.teenagecancertrust.org/
|
|
|
stevebubs
|
| posted on 14/12/11 at 11:36 AM |
|
|
http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452
|
|
|
stevebubs
|
| posted on 14/12/11 at 11:36 AM |
|
|
If you're running under IIS then I believe the user for the connection will be the ISS Service Account.
|
|
|
TimEllershaw
|
| posted on 14/12/11 at 11:43 AM |
|
|
quote:
Originally posted by stevebubs
If you're running under IIS then I believe the user for the connection will be the ISS Service Account.
Yes, by default, it will be IUSR_xxx for asp stuff and Network_Service for .net stuff
...unless they are using authentication for the web site in general
... or have changed it in IIS
... or are impersonating
In my case I was authticating users on the web site and wanted to pass those user names on to the SQL server for authentication. Had problems getting
IIS to pass it on.
I feel your pain 
http://www.teenagecancertrust.org/
|
|
|
stevebubs
|
| posted on 14/12/11 at 11:48 AM |
|
|
quote:
Originally posted by TimEllershaw
quote:
Originally posted by stevebubs
If you're running under IIS then I believe the user for the connection will be the ISS Service Account.
Yes, by default, it will be IUSR_xxx for asp stuff and Network_Service for .net stuff
...unless they are using authentication for the web site in general
... or have changed it in IIS
... or are impersonating
In my case I was authticating users on the web site and wanted to pass those user names on to the SQL server for authentication. Had problems getting
IIS to pass it on.
I feel your pain
That's possible but you need to switch to SQL authentication mode, and extract the Username they authenticated with as part of your IIS
script.
Been almost 10 years since I messed with this stuff...surprising how little things come back
[Edited on 14/12/11 by stevebubs]
|
|
|
mads
|
| posted on 14/12/11 at 01:18 PM |
|
|
quote:
Originally posted by Pdlewis
as long as they are both on the same domain you should be fine
what authenication are you using for the website?
windows/forms/none?
none
I should also point out that IIS is running on a version of MS Windows Server Enterprise 2007 SP2 - I highlight this cos I have been trying to follow
some of the links mentioned and when it is asking to go to the directory security tab via properties, I can't cos I cant find it.
[Edited on 14/12/11 by mads]
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
mads
|
| posted on 14/12/11 at 01:49 PM |
|
|
Following the instructions from the link Tim gave, I now get the following error message when trying to load the site in IE whilst on the IIS box:
 
asp_sql_error
At least its a step forward but obviously still not quite right.
I have done the following to get to this message:
Disabled anonymous authentication
Enabled ASP.NET authentication and put the username/password in there
Changed the connection string to:
code:
Set cnn = Server.CreateObject("ADODB.Connection"
cnn.open
"workstation id=wwrhpharmiis01;packet size=4096;
Trusted_Connection=Yes;data source=WCLUSSQLOCS\WOCSSQL_INSTANCE";
persist security info=False;initial catalog=DrugLocator"
%>
Changed the webconfig file to:
code:
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<identity impersonate="true"/>
</configuration>
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
