|
security
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Web Applicaiton using Trusted Connections to SQL on different machine?Connections. If my SQL server and Web Server are on the same box I can get it to work. There needs to be a User on the SQL Server that in running the Web Application (in my case the ASPNET or Network Services account). So on the SQL and Web on the same box I can do this as the ASPNET or Network Services Accounts are local the Server and can be added to the SQL Server's Logins. Though the local Server accounts cannot be added to a remote SQL Server machine. I need to have a Domain Account to do this. I've Setup a separate Application Pool with a an Identity of a Domain User. Given the Domain User Local Admin Perms on the Web Server and Access to the SQL Server Databases that it needs. Though when I go to run the Application I do not see the Identity of the new Domain user in the list of Tasks for the w3wp.exe. I still see the local IWAM account. What am I doing Wrong? Thanks, Scott<- Scott Townsend wrote:
Show quoteHide quote > I'm trying to get my Webserver to talk to my SQL Server and allow Well, your latest mistake <grin> is posting to a classic ASP group (I'm > Trusted Connections. > > If my SQL server and Web Server are on the same box I can get it to > work. There needs to be a User on the SQL Server that in running the > Web Application (in my case the ASPNET or Network Services account). > > So on the SQL and Web on the same box I can do this as the ASPNET or > Network Services Accounts are local the Server and can be added to > the SQL Server's Logins. Though the local Server accounts cannot be > added to a remote SQL Server machine. I need to have a Domain > Account to do this. > > I've Setup a separate Application Pool with a an Identity of a Domain > User. Given the Domain User Local Admin Perms on the Web Server and > Access to the SQL Server Databases that it needs. > > Though when I go to run the Application I do not see the Identity of > the new Domain user in the list of Tasks for the w3wp.exe. I still > see the local IWAM account. > > What am I doing Wrong? > reading this in .inetserver.asp.db) when you should be posting to a dotnet group such as microsoft.public.dotnet.framework.aspnet. As for what you are doing wrong in your .Net app, I really can't say for sure. Here are some KB articles that may help: http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLC,GGLC:1969-53,GGLC:en&q=site:support%2Emicrosoft%2Ecom+%22SQL+Server%22+trusted+connection+ASP%2ENet If you don't find your answer there, post more information to the dotnet newsgroup where you are more likely to find someone who can help (I'm just beginning with .Net myself, so my ability to help is limited) Bob Barrows -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" Thank you for your reply.
I'm not sure if this is really .Net Related though. I get the Error if I use ASP.NET code to make the connection, or if i just use ASP code to make the connection. My ASP code looks like this: Set enm = Server.CreateObject("adodb.connection") enm.Open "Driver={SQL Server};Server=SERVER;Trusted_Connection=yes;Database=Northwind;" Set rsUser = enm.Execute("SELECT * FROM customers") Set enm = Nothing I get the Error on the .Execute Thanks, Show quoteHide quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:e2EDQO7gFHA.3656@TK2MSFTNGP09.phx.gbl... > Scott Townsend wrote: >> I'm trying to get my Webserver to talk to my SQL Server and allow >> Trusted Connections. >> >> If my SQL server and Web Server are on the same box I can get it to >> work. There needs to be a User on the SQL Server that in running the >> Web Application (in my case the ASPNET or Network Services account). >> >> So on the SQL and Web on the same box I can do this as the ASPNET or >> Network Services Accounts are local the Server and can be added to >> the SQL Server's Logins. Though the local Server accounts cannot be >> added to a remote SQL Server machine. I need to have a Domain >> Account to do this. >> >> I've Setup a separate Application Pool with a an Identity of a Domain >> User. Given the Domain User Local Admin Perms on the Web Server and >> Access to the SQL Server Databases that it needs. >> >> Though when I go to run the Application I do not see the Identity of >> the new Domain user in the list of Tasks for the w3wp.exe. I still >> see the local IWAM account. >> >> What am I doing Wrong? >> > Well, your latest mistake <grin> is posting to a classic ASP group (I'm > reading this in .inetserver.asp.db) when you should be posting to a dotnet > group such as microsoft.public.dotnet.framework.aspnet. > > As for what you are doing wrong in your .Net app, I really can't say for > sure. Here are some KB articles that may help: > http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLC,GGLC:1969-53,GGLC:en&q=site:support%2Emicrosoft%2Ecom+%22SQL+Server%22+trusted+connection+ASP%2ENet > > If you don't find your answer there, post more information to the dotnet > newsgroup where you are more likely to find someone who can help (I'm just > beginning with .Net myself, so my ability to help is limited) > > Bob Barrows > > -- > Microsoft MVP - ASP/ASP.NET > Please reply to the newsgroup. This email account is my spam trap so I > don't check it very often. If you must reply off-line, then remove the > "NO SPAM" > Scott Townsend wrote:
> Thank you for your reply. You should use the native OLE DB provider, not ODBC:> > I'm not sure if this is really .Net Related though. I get the Error > if I use ASP.NET code to make the connection, or if i just use ASP > code to make the connection. > > My ASP code looks like this: > > Set enm = Server.CreateObject("adodb.connection") > enm.Open "Driver={SQL > Server};Server=SERVER;Trusted_Connection=yes;Database=Northwind;" enm.open "provider=sqloledb;Integrated Security=SSPI;" & _ "Data Source=SERVER;Initial Catalog=Northwind" > Set rsUser = enm.Execute("SELECT * FROM customers") On the Execute? If it was a problem with the connection itself, the Open> Set enm = Nothing > I get the Error on the .Execute > statement would be throwing the error, not the Execute. What is the error? Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. I've changed the Driver to Provider and set it to sqloledb. I get the Same
Errors. And yes, you are correct, it was on the Open, not the Execute. Depending if I set the server to our backup Production SQL Server or the Production SQL Server I get the following: ---backup Production SQL Server Error Type: Microsoft OLE DB Provider for SQL Server (0x80040E4D) Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. ---Production SQL Server Error Type: Microsoft OLE DB Provider for SQL Server (0x80004005) Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Then I do a "Response.Write Request.ServerVariables("logon_user")" I get the proper Domain\UserID of who is logged into the Local Machine that I want passed to the Webserver to Pass then to the SQL Server. Thanks you, Scott<- Show quoteHide quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:ONzZn%23jhFHA.3568@TK2MSFTNGP10.phx.gbl... > Scott Townsend wrote: >> Thank you for your reply. >> >> I'm not sure if this is really .Net Related though. I get the Error >> if I use ASP.NET code to make the connection, or if i just use ASP >> code to make the connection. >> >> My ASP code looks like this: >> >> Set enm = Server.CreateObject("adodb.connection") >> enm.Open "Driver={SQL >> Server};Server=SERVER;Trusted_Connection=yes;Database=Northwind;" > > You should use the native OLE DB provider, not ODBC: > > enm.open "provider=sqloledb;Integrated Security=SSPI;" & _ > "Data Source=SERVER;Initial Catalog=Northwind" > > >> Set rsUser = enm.Execute("SELECT * FROM customers") >> Set enm = Nothing >> I get the Error on the .Execute >> > > On the Execute? If it was a problem with the connection itself, the Open > statement would be throwing the error, not the Execute. > > What is the error? > > Bob Barrows > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > I can't seem to reproduce your problem. Here is my attempt:
<% dim cn, rs, sSQL set cn = server.CreateObject("ADODB.Connection") cn.Open "provider=sqloledb;data source=clndwdev;" & _ "Integrated Security=SSPI;Initial Catalog=Northwind" sSQL = "Select suser_sname()" set rs = cn.Execute(sSQL,,1) Response.Write rs.GetStringrs.close:set rs=nothing cn.Close:set cn=nothing %> My website has Anonymous turned off, and Integrated Windows Authentication on. Running the page results in my login being written to response. It seems we are at an impasse. My only suggestion is to verify that your SQL Servers are up-to-date with their service packs. I seem to recall that one of the service packs fixed an issue with trusted connections... Bob Barrows Scott Townsend wrote: Show quoteHide quote > I've changed the Driver to Provider and set it to sqloledb. I get the > Same Errors. And yes, you are correct, it was on the Open, not the > Execute. > > Depending if I set the server to our backup Production SQL Server or > the Production SQL Server I get the following: > > ---backup Production SQL Server > Error Type: > Microsoft OLE DB Provider for SQL Server (0x80040E4D) > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. > > > ---Production SQL Server > Error Type: > Microsoft OLE DB Provider for SQL Server (0x80004005) > Login failed for user '(null)'. Reason: Not associated with a trusted > SQL Server connection. > > Then I do a "Response.Write Request.ServerVariables("logon_user")" I > get the proper Domain\UserID of who is logged into the Local Machine > that I want passed to the Webserver to Pass then to the SQL Server. > > > Thanks you, > Scott<- > > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message > news:ONzZn%23jhFHA.3568@TK2MSFTNGP10.phx.gbl... >> Scott Townsend wrote: >>> Thank you for your reply. >>> >>> I'm not sure if this is really .Net Related though. I get the >>> Error if I use ASP.NET code to make the connection, or if i just >>> use ASP code to make the connection. >>> >>> My ASP code looks like this: >>> >>> Set enm = Server.CreateObject("adodb.connection") >>> enm.Open "Driver={SQL >>> Server};Server=SERVER;Trusted_Connection=yes;Database=Northwind;" >> >> You should use the native OLE DB provider, not ODBC: >> >> enm.open "provider=sqloledb;Integrated Security=SSPI;" & _ >> "Data Source=SERVER;Initial Catalog=Northwind" >> >> >>> Set rsUser = enm.Execute("SELECT * FROM customers") >>> Set enm = Nothing >>> I get the Error on the .Execute >>> >> >> On the Execute? If it was a problem with the connection itself, the >> Open statement would be throwing the error, not the Execute. >> >> What is the error? >> >> Bob Barrows >> -- >> Microsoft MVP -- ASP/ASP.NET >> Please reply to the newsgroup. The email account listed in my From >> header is my spam trap, so I don't check it very often. You will get >> a quicker response by posting to the newsgroup. -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. Thank you for your reply. The only way I've been able to get it to work is
to turn off Integrated and turn on Basic Text Authentication. Is your SQL Server on a physically different machine then your Webserver? If they are on the same machine it seems to work jsut fine with Integrated Authentication. Its when its on a different machine that it does not.. This isn't the most recent one that i found, but I cant seem to find the one I did the other day. This is for IIS5 and SQL6.5 but pretty much says the same thing. http://support.microsoft.com/kb/176379/EN-US It would be nice to be able to use Integrated Authentication, But this will work. It prompts them for a Username and Password, so that way if someone sits down at the person's desk who has access to the data, they still need to enter in a username/password. Of course its in clear text, but I think the chances of someone here being able to packet sniff the traffic between the two machines is pretty slim with all the switches in-between. Thank you for your assistance. Scott<- Show quoteHide quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:O1cNIT8iFHA.3296@TK2MSFTNGP10.phx.gbl... >I can't seem to reproduce your problem. Here is my attempt: > > <% > dim cn, rs, sSQL > set cn = server.CreateObject("ADODB.Connection") > cn.Open "provider=sqloledb;data source=clndwdev;" & _ > "Integrated Security=SSPI;Initial Catalog=Northwind" > > sSQL = "Select suser_sname()" > > set rs = cn.Execute(sSQL,,1) > Response.Write rs.GetStringrs.close:set rs=nothing > cn.Close:set cn=nothing > %> > > My website has Anonymous turned off, and Integrated Windows Authentication > on. Running the page results in my login being written to response. > > It seems we are at an impasse. My only suggestion is to verify that your > SQL > Servers are up-to-date with their service packs. I seem to recall that one > of the service packs fixed an issue with trusted connections... > > Bob Barrows > > > Scott Townsend wrote: >> I've changed the Driver to Provider and set it to sqloledb. I get the >> Same Errors. And yes, you are correct, it was on the Open, not the >> Execute. >> >> Depending if I set the server to our backup Production SQL Server or >> the Production SQL Server I get the following: >> >> ---backup Production SQL Server >> Error Type: >> Microsoft OLE DB Provider for SQL Server (0x80040E4D) >> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. >> >> >> ---Production SQL Server >> Error Type: >> Microsoft OLE DB Provider for SQL Server (0x80004005) >> Login failed for user '(null)'. Reason: Not associated with a trusted >> SQL Server connection. >> >> Then I do a "Response.Write Request.ServerVariables("logon_user")" I >> get the proper Domain\UserID of who is logged into the Local Machine >> that I want passed to the Webserver to Pass then to the SQL Server. >> >> >> Thanks you, >> Scott<- >> >> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message >> news:ONzZn%23jhFHA.3568@TK2MSFTNGP10.phx.gbl... >>> Scott Townsend wrote: >>>> Thank you for your reply. >>>> >>>> I'm not sure if this is really .Net Related though. I get the >>>> Error if I use ASP.NET code to make the connection, or if i just >>>> use ASP code to make the connection. >>>> >>>> My ASP code looks like this: >>>> >>>> Set enm = Server.CreateObject("adodb.connection") >>>> enm.Open "Driver={SQL >>>> Server};Server=SERVER;Trusted_Connection=yes;Database=Northwind;" >>> >>> You should use the native OLE DB provider, not ODBC: >>> >>> enm.open "provider=sqloledb;Integrated Security=SSPI;" & _ >>> "Data Source=SERVER;Initial Catalog=Northwind" >>> >>> >>>> Set rsUser = enm.Execute("SELECT * FROM customers") >>>> Set enm = Nothing >>>> I get the Error on the .Execute >>>> >>> >>> On the Execute? If it was a problem with the connection itself, the >>> Open statement would be throwing the error, not the Execute. >>> >>> What is the error? >>> >>> Bob Barrows >>> -- >>> Microsoft MVP -- ASP/ASP.NET >>> Please reply to the newsgroup. The email account listed in my From >>> header is my spam trap, so I don't check it very often. You will get >>> a quicker response by posting to the newsgroup. > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup. > > 
Other interesting topics
Classic ASP page gives 'The page cannot be found' in IIS6.0
Giving Inetrnet Guest account full access to folders Problem registering IISUBA.dll Please Help! - Advanced Digest With IIS6 - 401.1 IIS Lockdown Tool HTW file security risk setting web site permissions IIS 6.0 Kerberos authentication Force Browser to Cache Images under SSL? SSL HTTPS works at first, then very slow or stops after some time? |
|||||||||||||||||||||||
