Web Applicaiton using Trusted Connections to SQL on different machine?

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?

Thanks,
Scott<-

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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 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"

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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 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"

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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=Northw ind;"
Set rsUser = enm.Execute("SELECT * FROM customers")
Set enm = Nothing
I get the Error on the .Execute

Thanks,

"Bob Barrows [MVP]" 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 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"
>

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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=Northw ind;"
Set rsUser = enm.Execute("SELECT * FROM customers")
Set enm = Nothing
I get the Error on the .Execute

Thanks,

"Bob Barrows [MVP]" 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 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"
>

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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=Northw ind;"

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.

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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=Northw ind;"

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.

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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]" 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=Northw ind;"
>
> 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.
>
>

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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]" 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=Northw ind;"
>
> 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.
>
>

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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]" 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=Northw ind;"
>>
>> 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.

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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]" 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=Northw ind;"
>>
>> 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.

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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<-
"Bob Barrows [MVP]" 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]" 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=Northw ind;"
>>>
>>> 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.
>
>

Re: Web Applicaiton using Trusted Connections to SQL on different machine?

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<-
"Bob Barrows [MVP]" 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]" 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=Northw ind;"
>>>
>>> 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.
>
>