Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do i connect my ASP site to SQL Server using trusted authenticatio 2

Status
Not open for further replies.
jojo11

jojo11

Programmer
Feb 2, 2003
189
US
We are trying to create standards to avoid plain text unames and pwords in configuration files and in .NET we have setup the standard for using trusted authentication and creating a limited access windows service account setup in IIS.

However the question was raised if we could do the same thing for traditional ASP applications? Are there any differenced? I know I will not be able to encrypt the config line as I would using the .NEt tool in the web.config to avoid showing the SQL server name, however we have a workaround for that.

Is there a good standard for using trusted in traditional ASP where we are using either anonymous access or Windows integrated?

-------------------------------------------
Ummm, we have a bit of a problem here....
 
Sort by date Sort by votes
An easy way to hide database login credentials is make a compiled ActiveX/COM object with a public method that returns an open ADO connection object.

If you want to use SQL Server integrated security, then the ASP will need to execute in the security context of the account with database access... obviously if your web server and SQL server are on different machines this would a problem if your ASP runs as the default IUSR_<MachineName> or another gimped local service account.

If you are willing to turn off anonymous and use windows integrated then just the regular SSPI connection string will be fine since each ASP will be executing in the security context of the domain user... who will have (or not have) access to your SQL server.


See:
 
Upvote 0 Downvote
Thanks so much for this. I got further because of your help. The problem I am having now I believe is somewhere in the IIS setup. I am getting the following message.

Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][SQL Native Client][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Can you tell me how to setup IIS? My SQL is remote but in my domain. I am running this from my local IIS. I use ananoymous access and type in my corp domain credentials for my NT service account which has permission to the database.

Code: 
<html>

<head>
	<title>Using NT Authentication</title>
</head>
<!--#include file="adovbs.inc"-->
	<body bgcolor="white" text="black">
		<%
		'Dimension variables
		Dim strMessage
		Dim objConn
		Dim strConnection
		DIM mySQL
		DIM objRS
		
		strConnectTrusted = "Driver={SQL Native Client};Server=NAAURM81B;Initial Catalog=mydatabase;Trusted Connection=yes;"

        Set objConn = Server.CreateObject("ADODB.Connection")
        objConn.Open strConnectTrusted
        
        
        Set objRS = Server.CreateObject("ADODB.Recordset")
        
        objRS.Cursortype = 1 
        objRS.Locktype = 3 
        objRS.Open mySQL, objConn

		'Place the value database value into the variable strMessage
		strMessage = Response.Write("<b>Brand Name:</b> " + objRS("BRAND_NAME") + "<br><br><b>Brand Description:</b> " + objRS("BRAND_DESCRIPTION")) 

 		'Write the contents of the variable strMessage to the web page 
		Response.Write (strMessage) 

		'Write line break into the web page
		Response.Write ("<br><br>")

		'Write the server time on the web page using the VBScript Time() function
		Response.Write ("The time on the server is: " & Time())

        ' Don't forget to close your connection after you display your data.
        objRS.Close
        Set objRS = Nothing
        objConn.Close
        Set objConn = Nothing


		'Close the server script	
		%>

	</body>
</html>

-------------------------------------------
Ummm, we have a bit of a problem here....
 
Upvote 0 Downvote
Try using the OLEDB trusted connection instead of the ODBC version... The sample below works for me:
Code: 
<html>
<head>
  <title>Using NT Authentication</title>
</head>
  <body bgcolor="white" text="black">
    <%
    'Dimension variables
    Dim objConn
    Dim strConnection
    DIM objRS

    'strConnectTrusted = "Driver={SQL Native Client};" _
    '                  & "Server=xxxxx;" _
    '                  & "Initial Catalog=yyyyy;" _
    '                  & "Trusted Connection=yes;"
                      
    strConnectTrusted = "Provider=sqloledb;" _
                      & "Data Source=xxxxx;" _ 
                      & "Initial Catalog=yyyyy;" _
                      & "Integrated Security=SSPI;"
    Set objConn = Server.CreateObject("ADODB.Connection")

    objConn.Open strConnectTrusted
    
    Set objRS = objConn.Execute("SELECT GetDate()")
    Response.Write "Server datetime: " & objRS(0)

    ' Don't forget to close your connection after you display your data.
    objRS.Close
    Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing
    %>
  </body>
</html>
 
Upvote 0 Downvote
Everything works perfectly now. Thanks. I was able to get ODBC working the same so I had a bug in my code.

I should tell you that if you look over the web there are many who think that it can't be done. So congrats and thanks.

-------------------------------------------
Ummm, we have a bit of a problem here....
 
Upvote 0 Downvote
One more question. With anonymous selected, can I still use windows integrated security so I can retrieve the NT account using the website? So I would have both checkboxes in IIS set? I have heard that it will try and use anonymous first hence losing the NT user information.

-------------------------------------------
Ummm, we have a bit of a problem here....
 
Upvote 0 Downvote
One thing you can do it make a different page in your site use only integrated security... after that the browser should automagically send the credentials on subsequent Requests to the same server.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
236
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
257
xwb
xwb
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
190
penguinspeaks
penguinspeaks
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
484
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
179
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top