Print Page - Connect to MS SQL

Title: Connect to MS SQL
Post by: seandelaney on May 25, 2006, 10:45:50 AM

Page dont seem to do anything? do i have anything wrong?

im trying to connect to MS SQL server 2000 by using windows NT authenication only?

Code:

<%
dim adoConn

set adoConn = server.createobject("adodb.connection")

adoConn.open "Provider=SQLOLEDB;Data Source=FILE04ABZPI;Initial Catalog=Personnel;Integrated Security=SSPI;"

if (adoConn) then
Response.Write("yes connect")
else
Response.Write("no failed")
end if

adoConn.close()
%>

Title: Re: Connect to MS SQL
Post by: rdivilbiss on May 25, 2006, 07:00:35 PM

Quote from: "seandelaney"

Do i have anything wrong?

Maybe...or else it would work. What is wrong may be not obvious.

Quote from: "seandelaney"

im trying to connect to MS SQL server 2000 by using windows NT authenication only?

Several things need to occur for this to be successful. I do not like SSPI for web applications, but have done this in the past and will replicate on my development network if necessary. But (arggggg) before that...

Can you connect using an SQL user id and password?

Something like this:

Code:

<%
Dim conn, rs, cmdText
cmdText = "SELECT TOP 10 id FROM testTable"

Set conn = Server.CreateObject("ADODB.Connection")
on error resume next
conn.open "Provider=SQLOLEDB;Server=olympus;User ID=webapp;Password=webapp;Database=examples;"
' *** SSPI Use \\ServerName or ServerName rather than the
' name of a File or System DSN for testing.
' conn.open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=examples;Integrated Security=SSPI;"
if err then
response.write err.number & " " & err.description & " " & err.source & " " & vbLF
response.end
else
response.write "Connected to database " & vbLF
Set rs = conn.execute(cmdText)
if err then
response.write err.number & " " & err.description & " " & err.source & " " & vbLF
response.end
else
response.write "Opened Database Okay <hr>" & vbLF
if NOT (rs.eof AND rs.bof) then
while not rs.eof
response.write rs("id") & " " & vbLF
rs.movenext
wend
response.write("<hr> ") & vbLF
else
response.write "No records returned " & vbLF
end if
rs.close
Set rs=nothing
end if
end if
conn.close
Set conn=nothing
%>

Also note that to revert back to SSPI only requires changing one line and the database will tell you what it is complaining of.(Probably: SQL Server does not exist or access denied.)

Waiting to here back.

Title: Connect to MS SQL
Post by: seandelaney on May 26, 2006, 01:15:28 AM

Quote

Can you connect using an SQL user id and password?

No, I was asked to connect by windows authenication only. The page runs on IIS, but guest users and NT authenications are both enabled.

The message from the debugger is:

----------------------------------------------------------------------------------------------------------
Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
/sandpit/index.asp, line 6

Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727)

Page:
GET /sandpit/index.asp

Time:
Thursday, May 25, 2006, 6:02:13 PM
-----------------------------------------------------------------------------------------------------------------

now i know this message is telling me - disable guest user and it will work, but im needing to be able connect just by using JUST windows authenication?

i know guest account is the problem, but we run 6 internal sites on this single server and we have the guest account enabled so allow our clients (who log into our system through citrix server) to view them.

this sites are document management systems which allow our oil and offshore engineers communicate and manage document remotely....

p.s please note that all these sites can only be accessed internally:

http://192.68.0.2/site1/index.asp
http://192.68.0.2/site2/index.asp
....
....
http://192.68.0.2/site5/index.asp
http://192.68.0.2/site6/index.asp

but our 7th site is our intranet and our companies IT manager in the UK has decided to make life hard for me and want a "personel" page where users can view/update their details such as address etc stored AD

querying AD is fine - i can do this no problem - its knowing which user on our network is viewing the webpage is the problem.

I have previously got it working by java applets and using a signed JAR file, but our IT manager dislikes using signed JAR file as it requires user to accept/trust this certificate to use the JAR across our network and has suggested using MS SQL's SYSTEM_USER command to return the username of the user viewing the page, but before we can execute this command/stored procedure in MS SQL, i have to try connect to MS SQL by using windows authenicaton first.

yor probably saying - IT manager is a ***** - why not just use the working solution? well, i dont know why!

then you'll ask why only connect by windows auth, well - the reason is if i connect using a servername, username and password like "FILE04ABZPI", "SD" & "12345" the MS SQL command SYSTEM_USER returns "SD" each time as it is the username we connect with...

MS SQL server allows NT Windows Authenication connections hence why we are trying to connect this way, so SYSTEM_USER returns NT Login Username each time...

sorry for the long explaination - but if you are going to give me the best advice, its best you know everything im trying to do ;-)

so the millon doller question is can it be done?

:?

Title: Connect to MS SQL
Post by: VGR on May 26, 2006, 02:29:05 AM

I think I already answered a question like this not long ago. Personally, I would go to the network admin and ask if a login script could be added to the ones used upon AD/LAN log in time.

I would just execute a BATch that sends the USERNAME (or machine name) environment variable to the 192.168.0.2 webserver, and then I would just store that "IP address x.x.x.x is linked to user USERNAME" in a table.

Then if the user connects to the intranet seventh site, I would use that table to authenticate him/her.

This would work nice.

Title: Connect to MS SQL
Post by: seandelaney on May 26, 2006, 03:04:56 AM

do you want my IT manager's email address to convience him?? :wink: :wink: joke btw..

i'd love to say, yes i will do this, but not yet - any other options/ideas/advice...

Title: Connect to MS SQL
Post by: seandelaney on May 26, 2006, 04:34:35 AM

OK i have created a DSN link to connect to MS SQL. in the DSN link, i have only selected "using windows NT authenication network ID only". when i do the test at the end, it passes.

but how do i use this in my code:

i have tried all these and none of them work...

Code:

<%
Dim adoConn

Set adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open "DATABASE=Personnel;DSN=intranet;UID=;Password=;"
If adoConn.errors.count = 0 Then
Response.write "Connected OK"
End If
adoConn.close()
%>

Code:

<%
Dim adoConn
adoConn.Open "DSN=intranet;Provider=SQLOLEDB;Initial Catalog=Personnel;Integrated Security=SSPI;"
If adoConn.errors.count = 0 Then
Response.write "Connected OK"
End If
adoConn.close()
%>

what have i done wrong?

Title: Connect to MS SQL
Post by: rdivilbiss on May 26, 2006, 09:00:23 AM

Quote from: "seandelaney"

Quote

Can you connect using an SQL user id and password?

No, I was asked to connect by windows authenication only. The page runs on IIS, but guest users and NT authenications are both enabled.

I asked for you to connect by SQL ID and password for troubleshooting purposes only. A first step if you will, as there are many things which could be the problem and that helps to eliminate some of those.

Quote from: "seandelaney"

The message from the debugger is:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

... I i know this message is telling me - disable guest user and it will work, but im needing to be able connect just by using JUST windows authenication?

In IIS you must disable the anonymous login and enable ONLY Integrated Windows Authentication for the _{site. If you fail to disable the anonymous login the Windows credentials will not be used.}

Quote from: "seandelaney"

i know guest account is the problem, but we run 6 internal sites on this single server and we have the guest account enabled so allow our clients (who log into our system through citrix server) to view them.

If anonymous logins are enabled the SQL server will not see DOMAIN\Guest as the user...it will see IUSR\machinename as the user, which is not a global (domain) account. It is less than guest, it is anonymous.

In IIS you can assign a DOMAIN account to be used for anonymous logins other than IUSR\Machine name.

So in your case, (with the CITRIX clients) you can assign the anonymous user for the web application to a NEW domain account called DOM\applicationName.

Then give the DOM\applicationName user basic access to the SQL server tables necessary for the application.

Quote from: "seandelaney"

but our 7th site is our intranet and our companies IT manager in the UK has decided to make life hard for me and want a "personel" page where users can view/update their details such as address etc stored AD

querying AD is fine - i can do this no problem - its knowing which user on our network is viewing the webpage is the problem.

Then you are back to disabling the anonymous user in IIS.

It has been a while, but it seemed to me that my banking applications that allowed remote access via Citrix, still required the remote user to log in using a NT Authority user ID and Password which was passed on the the internal apps, since in effect they were logging into the local NT authority.

Quote from: "seandelaney"

I have previously got it working by java applets and using a signed JAR file, but our IT manager dislikes using signed JAR file as it requires user to accept/trust this certificate to use the JAR across our network and has suggested using MS SQL's SYSTEM_USER command to return the username of the user viewing the page, but before we can execute this command/stored procedure in MS SQL, i have to try connect to MS SQL by using windows authenicaton first.

yor probably saying - IT manager is a ***** - why not just use the working solution? well, i dont know why!

No actually I tend to agree with the manager's thought process and do not understand why you have the anonymous user enabled for this type of application.

Quote from: "seandelaney"

then you'll ask why only connect by windows auth, well - the reason is if i connect using a servername, username and password like "FILE04ABZPI", "SD" & "12345" the MS SQL command SYSTEM_USER returns "SD" each time as it is the username we connect with...

Exactly, which is why in this case you need SSPI. That I don't like SSPI does not imply I have never used it or that it does not have a place.

Quote from: "seandelaney"

so the millon doller question is can it be done?

It is an all or nothing proposition as far as I know. If you want to pass the NT user to SQL you need to disable the anonymous user for the _site.

Title: Connect to MS SQL
Post by: rdivilbiss on May 26, 2006, 09:07:19 AM

(http://www.suspendedexpert.com/ert_forum/IIS_disableAnonymousUser.jpg)

Title: Connect to MS SQL
Post by: seandelaney on May 26, 2006, 09:27:17 AM

Quote

yes i can connect and query fine using a SQL login username and password...

i have followed what you said above by disble anonymous user and ticking authenicate by windows... and i still get Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

Code:

<%
Dim Conn

Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Personnel;Data Source=FILE04ABZPI"

If Conn.errors.count = 0 Then
Response.Write("Connected")
Else
Response.Write("Connection Failed")
End If
%>

Title: Connect to MS SQL
Post by: rdivilbiss on May 26, 2006, 03:23:49 PM

Does the page which is attempting the connection return the correct value for Request.ServerVariables("AUTH_USER")?

It sounds like the page does not recognize the NT user id at this point.

Title: Connect to MS SQL
Post by: seandelaney on May 27, 2006, 02:17:15 AM

yes, when i try view page, i get prompted for username and password s0 i enter these and then i get the login failed message.

i have tested to see if auth_user was set and it does get set as when i print it out is get "ABZPI\sdelaney" which is correct.

one thing im not sure about, is:

our domain is ABZPI and all our users are members of the Pegasus group : ABZPI\Pegasus. in the MS SQL server, there is only a login account called ABZPI\Pegasus and ABZPI\Administrator.

Does this matter? like what i mean is, my username is ABZPI\sdelaney and im apart of the ABZPI\Pegasus group but yet i cant login...

Title: Connect to MS SQL
Post by: rdivilbiss on May 28, 2006, 08:41:00 AM

I'll have to make some tests with groups....

Experts Round Table Network

Serverside Technology => ASP => Topic started by: seandelaney on May 25, 2006, 10:45:50 AM