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
Mads
What error is the ad authentication giving? Do you need to enter your username as a full string? "domainusername" and not just username? If
it tries to authenticate your username but doesn't know who to ask it would fail.
code:
Microsoft OLE DB Provider for SQL Server error '80040e4d'
Login failed for user 'WHITSdrugloc'.
/Calculators/Connections/druglocator.asp, line 11
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.,
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="domainuser" password="password" ></identity>
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?
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.
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.
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?
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
If you're running under IIS then I believe the user for the connection will be the ISS Service Account.
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.
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
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?
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=WCLUSSQLOCSWOCSSQL_INSTANCE";
persist security info=False;initial catalog=DrugLocator"
%>
code:
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<identity impersonate="true"/>
</configuration>