Connecting to SQL from IIS

Hi All,
I'm attempting to connect my .asp page to an SQL 2005 server and I'm
receiving the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for
user ''. The
user is not associated with a trusted SQL Server connection.
The SQL server is set for mixed authentication and my connection string
points to an ODBC connection on my web server (and it showed that it was
successfully able to connect after the DSN was created). Are there other
steps on my SQL server that need to be performed to allow for authentication
from IIS? I will also post my connection string below from my ASP page:
<%
Const adOpenStatic = 3
Const adLockReadOnly = 1
'Open Connection to Database
set Conn = Server.CreateObject("ADODB.Connection")
qryDSN = "DSN=BT"
Conn.open qryDSN
set rs = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT CO_COMPANY_NAME FROM COMPANIES"
rs.open sql, conn, adOpenStatic, adLockReadOnly
rs.movefirst
do while not rs.EOF
response.Write(rs.fields(0))
loop
%>
Any help is appreciated!!
Joe Corey
MCSE, MCSA, Security+
jcorey@.andrew.cmu.eduIs the DSN using a SQL Authen. or WIndows Authenticatio. I guess it is
using Windows Authentication and the SQL Server is not on the same box
like the IIS Server. If so, and my guess was right, you will have to
enabled the IIS to delegate authentication. More about this can be
found on the MSDN knowledge base article. Simply search for "Delegation
AND SQL SERVER". After setting up a security principal name SPN you
will be able to authenticate the users.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Connecting to Named Instances

We have a few servers with named instances, 2003 Server w/ SQL 2000 SP3a
running Named Pipes only.
Our users primarily use IIS 5, 5.1, and 6 to connect to these servers with
ColdFusion and ASP .NET applications. Right now the ASP .NET applications
aren't able to connect to the server, here is an example of the connection
string:
user id=myuser;data source="SQLserver\SQLinstance";initial
catalog=mydatabase;password=mypassword
This doesn't work, and we're not quite sure why. Does any one also have
some good resources on managing multiple instances of SQL server from a SQL
admin and SQL developer point of view?
Thanks,
Trevor
These articles will help you troubleshoot.
265808 INF: How to Connect to an SQL Server 2000 Named Instance with the
http://support.microsoft.com/?id=265808
286303 INF: Behavior of SQL Server 2000 Network Library During Dynamic Port
http://support.microsoft.com/?id=286303
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connecting to Named Instances

We have a few servers with named instances, 2003 Server w/ SQL 2000 SP3a
running Named Pipes only.
Our users primarily use IIS 5, 5.1, and 6 to connect to these servers with
ColdFusion and ASP .NET applications. Right now the ASP .NET applications
aren't able to connect to the server, here is an example of the connection
string:
user id=myuser;data source="SQLserver\SQLinstance";initial
catalog=mydatabase;password=mypassword
This doesn't work, and we're not quite sure why. Does any one also have
some good resources on managing multiple instances of SQL server from a SQL
admin and SQL developer point of view?
Thanks,
TrevorThese articles will help you troubleshoot.
265808 INF: How to Connect to an SQL Server 2000 Named Instance with the
http://support.microsoft.com/?id=265808
286303 INF: Behavior of SQL Server 2000 Network Library During Dynamic Port
http://support.microsoft.com/?id=286303
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connecting to Analysis Services using HTTP

my analysis server is hosted on a win2k server with IIS, the hosting page is
on another machine. I have followed everything as per MS KB article and I
could not connect through the analysis server using http. i have similar
implementation on my dev machine but the main difference is my OLAP is on
top of winxp. i just wanted to know if i am missing any other setting not
specified on the MS KB article on how to implement OLAP cube through HTTP
over win2k operating system.

note: i tried connecting to olap using the MDX sample application using
MSOLAP.2 provider and connecting through a simple office web component. I
think the best way to test the connection is through the MDX sample
application that comes with Analysis Server installation.Check security for IIS and to the C:\Program Files\Microsoft Analaysis
Server\Bin folder and hidden share. Without any error messages there
is little we can help you with.|||Adam,
I am able to access the msolap.asp through the website. that means I
have permission to the file on the IIS side.
I will be checking the permission on the Analysis Server folder.

Whenever I try to connect using the MDX Sample Application to the HTTP
server(with my cubes) the app seem to hang. after waiting
for about 2 minutes it gives me an error "unable to connect..."

"Adam J. Tybor" <adam.tybor@.gmail.com> wrote in message
news:1096106771.514490.178060@.k17g2000odb.googlegr oups.com...
> Check security for IIS and to the C:\Program Files\Microsoft Analaysis
> Server\Bin folder and hidden share. Without any error messages there
> is little we can help you with.

Connecting Sql Server 2005 on Windows 2003 using ASP local

Hello!
I'm migrating an IIS/SQL-Server application from Windows NT4.0 and SQL-
Server 2000 to Windows 2003 Server and SQL-Server 2005.

My problem is that it is not possible to connect local (IIS and SQL-
Server 2005 are runnng on the same node) using ODBC. Running the
applikation on a remote IIS (XPPro) all works fine. I can't see any
differences in the ODBC-configuration.
Any idea?

Thanks(kk035@.hotmail.com) writes:

Quote:

Originally Posted by

I'm migrating an IIS/SQL-Server application from Windows NT4.0 and SQL-
Server 2000 to Windows 2003 Server and SQL-Server 2005.
>
My problem is that it is not possible to connect local (IIS and SQL-
Server 2005 are runnng on the same node) using ODBC. Running the
applikation on a remote IIS (XPPro) all works fine. I can't see any
differences in the ODBC-configuration.

Error messages? Connection strings?

Running IIS and SQL Server on the same machine is not a very good idea,
from the point of view of performance and security. The latter is
particularly critical, if the web application is on the internet.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It's just an internal application without any performance problems.

strConnect="DSN=ODBC-datasourcename;UID=username;PWD=password"

code snippet:

set conDb = Server.CreateObject("adodb.connection")
conDb.Open Session("strConnect")
for each errDb in conDb.Errors
if errDb.Number <0 then
bDbError = true
Session("strErrTxt1") = errDb.Description
Session("strErrTxt2") = errDb.Source
Session("strErrTxt3") = errDb.SQLState
Session("strErrNbr1") = errDb.Number
Session("strErrNbr2") = errDb.NativeError
exit for
end if
errDb.Clear
next

-err.Db contains no information|||(kk035@.hotmail.com) writes:

Quote:

Originally Posted by

It's just an internal application without any performance problems.
>
strConnect="DSN=ODBC-datasourcename;UID=username;PWD=password"
>
code snippet:
>
set conDb = Server.CreateObject("adodb.connection")
conDb.Open Session("strConnect")
for each errDb in conDb.Errors
if errDb.Number <0 then
bDbError = true
Session("strErrTxt1") = errDb.Description
Session("strErrTxt2") = errDb.Source
Session("strErrTxt3") = errDb.SQLState
Session("strErrNbr1") = errDb.Number
Session("strErrNbr2") = errDb.NativeError
exit for
end if
errDb.Clear
next
>
-err.Db contains no information

But what happens? Does it wait for 15 seconds and give up? Or does attempts
to use the connection fail?

And why use a DSN? Personally, I've never liked DSNs, it just one more
source of error. What is in that DSN?

What happens if you change the DSN part to Provider=SQLNCLI?

Where does the password come from? Is it hard-coded into the
application, or user-entered? Keep in mind that in SQL 2005, passwords
are always case-sensitive.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 17 Feb., 15:55, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(k...@.hotmail.com) writes:

Quote:

Originally Posted by

It's just an internal application without any performance problems.

>

Quote:

Originally Posted by

strConnect="DSN=ODBC-datasourcename;UID=username;PWD=password"

>

Quote:

Originally Posted by

code snippet:

>

Quote:

Originally Posted by

set conDb = Server.CreateObject("adodb.connection")
conDb.Open Session("strConnect")
for each errDb in conDb.Errors
if errDb.Number <0 then
bDbError = true
Session("strErrTxt1") = errDb.Description
Session("strErrTxt2") = errDb.Source
Session("strErrTxt3") = errDb.SQLState
Session("strErrNbr1") = errDb.Number
Session("strErrNbr2") = errDb.NativeError
exit for
end if
errDb.Clear
next

>

Quote:

Originally Posted by

-err.Db contains no information

>
But what happens? Does it wait for 15 seconds and give up? Or does attempts
to use the connection fail?
>
And why use a DSN? Personally, I've never liked DSNs, it just one more
source of error. What is in that DSN?
>
What happens if you change the DSN part to Provider=SQLNCLI?
>
Where does the password come from? Is it hard-coded into the
application, or user-entered? Keep in mind that in SQL 2005, passwords
are always case-sensitive.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

It seems that it does not wait for 15 seconds - it returns at once.
The password is hard coded. Changing to SQLNCLI doesn't change
anything. Using the same ASP-application on a remote IIS without
changing anything works fine. Using a wrong password in the ODBC-
Configuration to get additional configuration options produces an
entry in the error log of the SQL-Server, which tells that the
password is wrong. Using a wrong password in the DSN for the ASP-
application does not result in an error log entry.|||(kk035@.hotmail.com) writes:

Quote:

Originally Posted by

It seems that it does not wait for 15 seconds - it returns at once.
The password is hard coded. Changing to SQLNCLI doesn't change
anything. Using the same ASP-application on a remote IIS without
changing anything works fine. Using a wrong password in the ODBC-
Configuration to get additional configuration options produces an
entry in the error log of the SQL-Server,

You are talking about the ODBC applet in the Control Panel?

Quote:

Originally Posted by

Using a wrong password in the DSN for the ASP-
application does not result in an error log entry.

A password in the DSN? Didn't you also have a password in the connection
string?

I still don't know why you think the connection failed.

If you leave out "if errDb.Number <0 then" do you get anything in
your Session array?

When you changed to SQLNCLI, did you remove the DSN reference?

What happens if you add "SERVER=bogus;" to the connection string?

What happens if you replace UID and PWD with "Integrated Security=SSPI;"?

Since I don't have to access to your system I have to ask questions in
the dark. Please keep this in mind when you answer the questions, and
try to be as clear and unambiguous as possible. It's enough of a guessing
game for me anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Connecting Securely?

Hi,
I have SQL Server 2000 running on one box and IIS running on another box. I
am connecting to the SQL Server via ASP pages on the IIS box. My concern is
security -- currently I am using this kind of code to connect:
var setListings = Server.CreateObject("ADODB.Recordset");
setListings.ActiveConnection =
"Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User
ID=USERID;Initial Catalog=tableName;Data Source=127.0.0.1"
My concern is that the username and password are being sent via plain text
from one box to the other.. Is this in fact the case? Is there another way
to make the connection? I think I could use Windows 2000 user accounts but
that would require trusted domains, etc. and neither box is a domain
controller (they are running at my ISP)
Thanks,
RichYou want to enable SSL for communication between the servers.
http://support.microsoft.com/defaul...kb;en-us;276553
-oj
http://www.rac4sql.net
"Richard Morey" <rwmorey71@.hotmail.com> wrote in message
news:ONWK0C4CEHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have SQL Server 2000 running on one box and IIS running on another box.
I
> am connecting to the SQL Server via ASP pages on the IIS box. My concern
is
> security -- currently I am using this kind of code to connect:
> var setListings = Server.CreateObject("ADODB.Recordset");
> setListings.ActiveConnection =
> "Provider=SQLOLEDB.1;Password=PASSWORD;Persist Security Info=True;User
> ID=USERID;Initial Catalog=tableName;Data Source=127.0.0.1"
> My concern is that the username and password are being sent via plain text
> from one box to the other.. Is this in fact the case? Is there another way
> to make the connection? I think I could use Windows 2000 user accounts but
> that would require trusted domains, etc. and neither box is a domain
> controller (they are running at my ISP)
> Thanks,
> Rich
>
>|||Hi,
I have quickly glanced at the link you provided and will read it more
closely later.. Quick question -- we have SSL on the web server, not on the
SQL Server - will this still work?
Rich
"oj" <nospam_ojngo@.home.com> wrote in message
news:%230cwcK4CEHA.1128@.TK2MSFTNGP11.phx.gbl...
> You want to enable SSL for communication between the servers.
> http://support.microsoft.com/defaul...kb;en-us;276553
> --
> -oj
> http://www.rac4sql.net
>
> "Richard Morey" <rwmorey71@.hotmail.com> wrote in message
> news:ONWK0C4CEHA.2908@.TK2MSFTNGP09.phx.gbl...
box.
> I
> is
Server.CreateObject("ADODB.Recordset");
text
way
but
>|||SSL will have to be (at least) on sqlserver box for it to work.
-oj
http://www.rac4sql.net
"Richard Morey" <rwmorey71@.hotmail.com> wrote in message
news:%23fmWqq5CEHA.3344@.tk2msftngp13.phx.gbl...
> Hi,
> I have quickly glanced at the link you provided and will read it more
> closely later.. Quick question -- we have SSL on the web server, not on
the
> SQL Server - will this still work?
> Rich
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%230cwcK4CEHA.1128@.TK2MSFTNGP11.phx.gbl...
> box.
concern
> Server.CreateObject("ADODB.Recordset");
> text
> way
> but
>

Connecting a web app to sql server

I have a web application running on Server A and need to connect to a SQL Server database on Server B.

In IIS on Server A, I have created a virtual directory called gdoc. For Directory Security I am using Integrated Windows Authentication. When I try to access the application, I get the following error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
/gdoc/properties/GDocProperty.asp, line 23

When I change IIS to allow anonymous access the error changes to:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
/gdoc/properties/GDocProperty.asp, line 23

Line 23 corresponds to the connection string, which is coming from a .udl file. In the .udl the connection string looks like this: Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GDoc;Data Source=TERRATAX

If I set up my web app on Server B then everything connects fine. It's running the web app on a different server that presents the problem.

This seems like it should be fairly routine, but I can't seem to figure out what I need to do.
The web app is NOT ASP.NET. It's classic .asp.

The reason for this is that the account you are using to connect to Server B(the one running SQL Server) is the IUSR_computernameServerA account (if this were ASP.NET, then the account is the ASPNET), which is the account used when you set IIS to anonymous access. In order to connect to SQL Server from Server A to Server B, you need to create an account named IUSR_computernameServerB on Server B and give this account permissions to access SQL Server. Or, better yet, use SQL authentication. Configure your SQL Server to use SQL authentication then create a SQL account and give that account permissions to the corresponding database. Then, in your connection string, use the credentials of the SQL account.

Hope this helps.|||I tried adding the account to SQL Server, but this is what I get this:

Windows NT User or Group '[domain name]/IUSR_TERRAGIS' not found.|||Have you tried SQL Authentication?