ODBC Login failed from outside the domain

[hawg]

I have a sql server 2000 installation on a Win2003 server. I am trying to connect via ODBC from my web server (a Win2000 box). I get error message: " Login failed for user '<username>' ".

I think the trouble is that the web server sits outside the domain from where the ss2k is installed. I can connect via ODBC (same login and ODBC configuration) from machines that are inside the domain. I have my sql server security settings to allow windows and sql logins.

Any thoughts? I have tried all I can think of.

Mike

 

[ArtieChoke]

Are you using an asp page to connect with (or how are you trying to connect)? Have you created a DSN on the web server to the database to test connectivity?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[NoCoolHandle]

The error looks like you are trying to authenticate using a windows login..

If you are trying to connect via a sqlserver login, it doesn't seem to be getting it. (aw heck, the error isn't quite specific enough - i would have expected to see a domain in that error, did you edit the error?)

If it is comming from your iis server the problem is likley to be the account name.. aspnet or iusr_computername.

If that account doesn't exist on your sql machine, add it to the server. Strangely enough if the account exists in more than one domain, as long as you have the same password, you can login. If it doesn't, you won't.

Problme with iis is that it likes to control passwords. Not a bad idea, but almost impossible to get the password out of that system so you can add it into your sqlserver system.....

Lots of intersesting things can go wrong..

you don't feel like sharing your actual connection string do you? Change the ServerName for security..



Rob

 

[hawg]

Apologies, I re-read my post -- I wasn't very specific.

I'm not making an ASP connection. Simply trying to set up a system DSN from within: Administrative tools --> Data Sources (ODBC).

My 'web server' as i called it, is a Win2000 box with Cognos tools that need to read data from the sql server 2000 instance on a Win2003 machine. The Cognos server is behind a firewall. The firewall is configured to allow traffic from any port on the Cognos server to port 1433 on the sql server machine.

The full error message is:
"
Connection failed:
SQLSTATE: '2800'
SQL Server ERROR: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]
Login failed for user 'myusername'.
"

It appears the tunnel through the firewall is working, and that sql server is rejecting the user. Sql server security is set to allow windows and sql logins and this same user connects from machines that are inside the firewall.

Thanks for the responses.

Mike

 

[NoCoolHandle]

I am guessing one of 2 problems..

1.If you are using Trusted Connections (Windows login), your pc (webserver) is trying to send the machine/username login to your 2003 server and failing because it is machine2/username..
Solution.. Try to login using a pure sqlserver login.. if that works, there might be a setting that stops it from trying to send the domain name with the connection attempt.

2. there is a chance that althought you have opened tcpip port 1433, your client (odbc) is trying to open a named pipe.

I am guessing here...

What I would do to find or fix that problem is in the odbc manager

In the second "Create a new Data..." dialog box there is a button that is titled
"Client Configuration"
Click on that and make sure that tcpip is the only "protocol" that you allow. Also here you might want to specify a port..

Other things that might be going wrong...

can the pc's see each other?
have you pinged?
In the old days I would have tried to use the makepipe/readpipe tool, but for a tcpip connection that wouldn't make any difference..

My main guess is authentication.. (wrong domain/user being passed to sQLserver)

let us know if either helped..


Rob

 

[SQLBill]

You are outside the domain, so it's not a trusted account. Therefore you will need to use SQL Server authentication, not Windows Authentication.

To use Windows Authentication, both systems (client and server) must authenticate via the same system. That means a common Domain Controller. Normally cross domains don't have that.

-SQLBill

 

[hawg]

Rob, SQLBill,

Thanks for the tips. I am using SQL authentication. The sql server that I want to connect to is one of 3 instances on the machine. I can connect to the localhost instance but can't to the desired instance.

Yesterday I finally figured out that the localhost was assigned to the default port (1433) and my instance is listening on another port, so I need to get with my network admin and ask him to allow communication across the firewall to the port assigned to my instance. Then change the client DSN to use this port.

I will let you know if this fixes the problem. I am still skepticle because yesterday I used the Sql Server Network utility to change my instance to port 1433 and still no go. But I am hoping that somehow if I come through the original port then it will work.

Mike

 

[NoCoolHandle]

There should be 2 great tools in your sqlserver program group that you need to examine.

"Client Network Utility"
and
"Server Network Utility"

Run the Client tools on your webserver and the server tools on your SQL box.

Also.. I Have found that SQL Profiler can be great at troubleshooting connection problmes. Run it and then try to connect. Then stop the trace and look at "how" your webserver is running.

Rob
PS just one note about the client network utitily. this allows you do do more easily what you can do in the odbc applet, client configuration button..


HTH

Rob

 

[hawg]

Well it turned out to be the port. My instance was not using the default (1433) port, once the client DSN and firewall were set to communicate on the correct port then my login worked.

I appreciate your tips -- I've learned several new things about sql server.

All the Best,
Mike

 

[hawg]

By the way, Microsoft Knowledge base article - 287932 "TCP Ports Needed for Communication to SQL Server through a Firewall", gives a good description of the communication process of sql server and the use of ports for both single and multiple instance setups.