How to Allow remote ASP scripts to access the server.

[chrigil]

After much messing about with Microsofts favorite error message:

"Login failed for user 'chris'. Reason: Not associated with a trusted SQL Server connection."

I am finally having some success which I haven't had previously, namely I can use a newly created Account to login to the SQL Server SQL Analyser but it still gives me the same old error message if I try using the account to access it from an ASP script hosted on another machine on my network.
I am assuming that the problem is something to do with the fact that IIS is running the script on one computer and SQL Server is running on the other.
This is really annoying and by the looks of the forums out there 100's of people are having similar problems.

Can somebody please tell me how to sort this problem out.



Thanks in advance,

Chris

 

[mrdenny]

Is your SQL Server setup for Windows Auth of Mixed Mode Auth?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[chrigil]

It was set up for Windows Auth but then I changed it to Mixed Mode following the advice of some forum suggestions and thats when I was able to use the Account to access SQL Server SQL Query Analyser but not via a script.

Any ideas?



Thanks in advance,

Chris

 

[mrdenny]

There may be something funky in your connection string. Can you post it please. It seams like it's trying to use the username and password you provided to log in with windows auth.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[chrigil]

Here is the connection string, it worked before when IIS and SQL Server was on the same computer but now that IIS is on 10.0.0.12 and SQLServer is on 10.0.0.9 it won`t work.
Maybe its not the reason but hopefully thats what you`ll be able to tell me ;-)

strConn = "Driver={SQL Server};Server=10.0.0.9;Database=kshsdb;Uid=chris;Pwd=1234;" 
Set oConn = server.createObject("ADODB.connection")
oConn.open strConn

Thanks in advance,

Chris

 

[mrdenny]

yeah, your connection string looks fine. Can you connect via the client tools on the web server? Try putting the host name in instead of the IP. Shouldn't matter but you never know.

Try checking the network config of the SQL Server. Make sure that TCP/IP is enabled. It may be trying to run over netbios or something.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[chrigil]

Excuse my ignorance but your reply gives me more questions than answers

1) By saying connect via client tools do you mean using SQL Query Analyser from a remote machine? If so the answer is no. I open SQL QA and look for a list of Servers on the Network and it doesn`t find any. I can however find a server from the ASP script cause it gives me errors that suggest it can find it

2) How would I put the hostname instead of the IP address? My pc is PC-LAPTOP would that have anything to do with the hostname?

3) I assume TCP/IP is installed because I can go to Network Adapters-->Properties-->TCP/IP etc and set a static IP Address

I appreciate the help



Thanks in advance,

Chris

 

[mrdenny]

1) Yes I mean query analyser. You can type in the machine name of the SQL Server in there.

2) The hostname is the machine name.

3) If you haven't change it TCP/IP should be enabled for SQL (You can disabled it in the network button in the Server properties).

Make sure that there are no firewalls setup on either machine or on any routers or switches between the machines.

What OS is each machine running?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[chrigil]

Putting PC-Laptop instead of 10.0.0.9 seems to do the same. That shouldn't be a problem though should it?

TCP/IP is definately enabled.

HOWEVER:

When I try connecting using the QA from the machine with IIS on using an account that definately works local and I get the following error message:

Unable to connect to Server PC-LAPTOP
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

I have no firewalls set up or anything so what could the problem be? Do you think the problem stems from IIS/ASP Script not being able to find the SQL Server or that access is denied?

Any ideas?

Thanks in advance,

Chris

 

[chrigil]

UPDATE

I just tried running a quick script to connect to the database using IIS on the SAME server as SQL Server is running i.e. 10.0.0.9 and it worked. The login was accepted.

Does this give you any clues?

Both machines are running XP Pro, however the 10.0.0.12 machine had a trial version of SQL Server on it that has expired. Could this be blocking the connection somehow?



Thanks in advance,

Chris

 

[JamesLean]

Can you even ping the SQL Server (either by hostname or IP address) from the web server?

--James

 

[chrigil]

It doesn't appear that I can ping the SQL Server. I am not entirely sure how to try to ping a particular port from the command line but I did perform a port scan on the computer using a Mac and 1433 didn't come up. Hide Server is NOT checked by the way so that isn't the problem.

1) How can I ping 10.0.0.9:1433 from the command line?

2) Why would the port scan show 1433 as not being open?



Thanks in advance,

Chris

 

[JamesLean]

If you can't even ping the server, there's really no use worrying about whether ports are open or not!

You need to fix your network connectivity problems (ie, make it so that the web server and sql server can see each other) then come back to whether you can connect to the database.

--James

 

[chrigil]

I can ping the machine with SQL Server on i.e. 10.0.0.9 but I can't connect to SQL Server on that machine.

Thanks in advance,

Chris

 

[JamesLean]

So when you said:

It doesn't appear that I can ping the SQL Server

what you actually meant was:

It does appear that I can ping the SQL Server

;-)

If you can ping it, you can test the port by typing:

telnet 10.0.0.9 1433

at a command prompt. If you get a blank screen with a flashing cursor then that is good. If you get an error it means that you may need to open that port on any firewall you may be going through.

--James

 

[chrigil]

Aha I guess what I actually meant was:

"It doesn't appear that I can ping the SQL Server but I can Ping the SQL Server Server"

This could get very confusing very quickly lol...


Anyway back to the problem, I tried Telnet 10.0.0.9 1433 and got the following error:

Connecting to 10.0.0.9...Could not open connection to the host, on port 1433: Connect failed

Any advice? All firewalls are off (SP2 Firewall is off).

Thanks in advance,

Chris

 

[JamesLean]

Firstly I would check that the SQL Server is listening on the default port (1433).

- On the SQL Server, go to Start -> Programs -> Microsoft SQL Server -> Server Network Utility.
- Make sure TCP/IP is in the Enabled Protocols box.
- In the TCP/IP properties check the default port and make sure "Hide server" is not ticked.

--James

 

[chrigil]

Hi James thanks for the help. I have checked those things with no results.

I have just checked the error log though and I'm no expert (stop laughing) but there seems to be something interesting on the line beginning 2005-03-15 10:12:51.93.
This would suggest to me that there is a problem with TCP/IP because firstly it doesn't say TCP/IP and secondly it should say listening on 10.0.0.9:1433

The complete log is shown below, ther is also 3 errors at the end of the log:

2005-03-15 10:12:43.62 server    Copyright (C) 1988-2000 Microsoft Corporation.
2005-03-15 10:12:43.62 server    All rights reserved.
2005-03-15 10:12:43.62 server    Server Process ID is 1428.
2005-03-15 10:12:43.62 server    Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server Data\MSSQL\log\ERRORLOG'.
2005-03-15 10:12:44.35 server    SQL Server is starting at priority class 'normal'(1 CPU detected).
2005-03-15 10:12:45.45 server    SQL Server configured for thread mode processing.
2005-03-15 10:12:45.48 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2005-03-15 10:12:45.54 server    Attempting to initialize Distributed Transaction Coordinator.
2005-03-15 10:12:50.46 server    Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
2005-03-15 10:12:50.59 spid3     Starting up database 'master'.
2005-03-15 10:12:51.64 server    Using 'SSNETLIB.DLL' version '8.0.194'.
2005-03-15 10:12:51.64 spid5     Starting up database 'model'.
2005-03-15 10:12:51.74 spid3     Server name is 'PC-LAPTOP'.
2005-03-15 10:12:51.85 spid8     Starting up database 'msdb'.
2005-03-15 10:12:51.90 spid9     Starting up database 'pubs'.
2005-03-15 10:12:51.93 server    SQL server listening on Shared Memory, Named Pipes.
2005-03-15 10:12:51.95 server    SQL Server is ready for client connections
2005-03-15 10:12:51.95 spid10    Starting up database 'Northwind'.
2005-03-15 10:12:51.98 spid11    Starting up database 'kshsDB'.
2005-03-15 10:12:52.78 spid5     Clearing tempdb database.
2005-03-15 10:12:55.07 spid5     Starting up database 'tempdb'.
2005-03-15 10:12:55.37 spid3     Recovery complete.
2005-03-15 10:21:04.32 spid51    Using 'xpstar.dll' version '2000.80.194' to execute extended stored procedure 'sp_MSgetversion'.
2005-03-15 10:21:09.43 spid51    Error: 15457, Severity: 0, State: 1
2005-03-15 10:21:09.43 spid51    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2005-03-15 10:21:09.71 spid51    Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure 'xp_msver'.
2005-03-15 11:31:37.10 spid53    Error: 15457, Severity: 0, State: 1
2005-03-15 11:31:37.10 spid53    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2005-03-15 13:50:52.71 spid51    Error: 15457, Severity: 0, State: 1
2005-03-15 13:50:52.71 spid51    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..

Thanks in advance,

Chris

 

[JamesLean]

To add to my previous post, if the port is 1433 then something on the network must be stopping the connection, namely a firewall somewhere.

If it's not 1433 then try telnet to whatever port it is and see if that works.

--James

 

[JamesLean]

Interesting. Are you sure you have TCP/IP in the enabled protocols in the server network utility?

--James