Allow another domain to connect to SQL Server 1

[esmithbda]

We have two domains in our company, one in the US (DomainB) and one outside of the US (DomainA). DomainB is a small office and only runs Windows 2003 SBS. DomainA has Windows 2003 Standard.

There is an always on firewall hardware to firewall hardware VPN connection between the two networks, and the DNS is setup in a way that we can see the machines on either side and ping them successfully from either side.

There are no trusts setup because as I understand it, SBS does not allow trusts and forces the single domain.

We have an application in DomainA which runs against a SQL Server database. Our users in our domain can use it just fine and it works great.

But our users in DomainB cannot connect to the SQL Server (they can ping it and name resolve it just fine, but the application won't allow the first stage authentication to it).

As far as I understand it, the application talks to the server and sets up a process by which to authenticate through SQL authentication. This brings up a login screen and then whatever is submitted in there is checked against the SQL logins in the system.
But DomainB gets an error message when trying to even bring up the login screen, which leads me to believe that it is an issue of our server denying the connection due to it being an outside domain.

So my question is, how can I force our SQL Server to allow all connections from that domain? If it is a trust relationship, can it be done with the SBS system?

 

[SQLBill]

You need to check a couple of things in Enterprise Manager...

1. Right click on the SQL Server Instance, select Properties, go to Security tab. What type of authentication is being used: Windows Only or Mixed Mode? It needs to be Mixed Mode.

2. If it's mixed mode, close that window, expand until you find Security. Expand that and find Logins. Any user or application logging in MUST have a Login here. Check to see that your remote users or application has a login AND that it is set to use SQL Server Authentication.

-SQLBill

Posting advice: FAQ481-4875

 

[esmithbda]

1) mixed mode (Windows and SQL Authentication

2) all of the logins are in there and they are all set to SQL Authentication.

Part of the issue with #2 is that this application appears to hit the database first as a fixed user (hardcoded into this application), and then from there that causes the application to bring up a login screen.
Then whatever is typed into the login screen, assuming it authenticates correctly, allows you into the application with that user's rights in the system.

The problem we are seeing is that if the users in the same domain as the SQL Server machine run the application, they get the login screen, and then they put in their login, and life is wonderful.
But if the users in the other domain run the application, they get an error message instead of the login screen.

The users in DomainB can ping the database machine, so the connection is present.
So my assumption is that they are having issues due to being in another domain.

I would just set a trust with that domain, but they are running Windows SBS 2003 on that domain and as I understand it (we tried), SBS doesn't allow trusts.

Is there a way around that? We don't necessarily need all of that which a trust would give us - we really only need the SQL Server to say "if they are coming from this IP range, then they are okay".
Really, to be honest, the SQL Server could really allow anyone and it should be okay - this is all inside our firewall - and they would need to get past the authentication screen anyway.

But as it is right now, they can't even get to that authentication screen - presumably due to the domain difference.
(I have sent the company an email trying to figure this out, but they are literally on the other side of the world from us, so there is a 12 hour delay in our conversations, and we are currently in a rather big time crunch due to the amount of data we are trying to port into this system)

 

[SQLBill]

Next thing to check....what type of computers are they using? Are they Windows XP boxes? If so, do they have SP2 installed and the Windows Firewall on? Windows Firewall is set to block TCP Port 1433 which SQL Server uses.

Have you checked the ODBC connection?

Can they telnet into the computer?

-SQLBill

Posting advice: FAQ481-4875

 

[esmithbda]

The client machines on both domains are XP boxes. I have sent them an email to see if they are SP2 or not, but I know the machine I am sitting at right now is SP2 and it can access it just fine (but I am in DomainA).

Our hardware firewall was allowing everything in the LAN to access 1433 on internal or external connections, so theoretically that VPN connection should have included that - but just to be sure sine some hardware firewalls have some different ideas about that sort of thing, I added a forced LAN to LAN allowance of port 1433.
Once I hear back from them, I will get them to try that out again.

There is no ODBC connection setup - the install process creates a registry file which we can edit to change what database machine and/or database on that machine it accesses.
The actualy ODBC connection string is probably hardcoded in the system, pulling out those variables from the registry.

I haven't had them try telnetting to the machine yet - I am assuming you mean to "telnet SERVER_IP 1433". I did that from my machine here and got a blank screen - which I would assume is success since things work from my end. I will get them to try that from their end and assume that getting an error message is bad, blank screen good.

 

[awaywifye]

You might be looking in the wrong place:

1) What NT rights do the users in DomainB have on your server?
2) What NT rights do the users in DomainB have on their own machines? We use a similar method (program has it's own SQL user). If the users don't have the correct NT rights on their machines, they can fire off the program, but it dies when trying to connect (SQL server authentication failed message).

 

[esmithbda]

awaywifye,

1) Technically I would love to give these users (DomainB) the same rights in my domain (DomainA) as regular users here have. But as I said before, they (DomainB) are on Windows SBS 2003. The only way I know of to give another domain rights is through a trust, and SBS doesn't allow trusts.
If I am wrong and there is another way to give them access, I am all ears.
There is a VPN connection between the two of us and we can see each others machines (ping) and resolve via nslookup. We are in the 192.168.0.X subnet, and they are in 192.168.168.X range.
Each of us have added the other to the foward/reverse lookup range in DNS.

2) I don't know what rights they have on their machines. I know here in the office they have admin rights on their local machines, but the other office is in another country so there is some lag in getting responses from them (they also are not technical, so it takes a few tries).

When you say they don't have the correct rights, what rights cause it to fail, and what would be success? I can get someone up there to add them to have full rights on their local machines, that should do it if that is the hold up.

 

[esmithbda]

I just heard back from them and he says that they have "Service Pack 4" on their machines which would indicate they are actually running Windows 2000.

Not sure if that matters.

 

[esmithbda]

The user in DomainB told me that he did "telnet SERVER_IP 1433" and the screen went blank - no error message. This is the same behavior I had when I did it on my machine (my machine runs the app just fine, so I would assume my setup is okay), so that sounds good I guess.

 

[SQLBill]

Okay, a bit more information gathering....

Have them access the database via the application. When it fails, check the following for any related messages...

SQL Server Error Logs
Windows Event Viewer Logs
-on the server with SQL Server
-on the distant machine trying to access SQL Server

Make sure the SQL Server Instance is 'auditting login attempts (failed and successful).

If you don't see a failed or successful login in SQL Server's Error log, then the client isn't getting that far.

If not, then see if there's failed login messages on the Windows box.

-SQLBill

Posting advice: FAQ481-4875

 

[esmithbda]

I don't see anything that looks like it would be it in the SQL Server Error logs (neither in the SQL part, nor in the Event Log on the server).
(it is auditing all login attempts)

I am having the user export his Event Logs and email them to me, so I will see if that has anything in there.


One solution to bypass all of this would be to have a separate machine to run this program and it joins our domain and then they use that to run the client. The problem with that is that more than one person in that office will want to run this at the same time.
Another option could be that they log out of their machine and log in on our domain - but again, that is annoying and they are going to complain.

 

[esmithbda]

No error messages in the user's Event Log either.

 

[SQLBill]

This doesn't appear to be a SQL Server issue. If you are auditing login failures/successes on the SQL Server instance (and you say you are), then you will see the logins or failures in the SQL Server Error Log.

My guess is their application's connection string isn't set up correctly. It doesn't appear to be making the connection to your server nor to the SQL Server instance.

-SQLBill

Posting advice: FAQ481-4875

 

[esmithbda]

I spoke with the developers who wrote the program and got more insight from them. They too agree that SQL should completely be allowing this and the domain issues are... well, non-issues, it shouldn't be a problem.

The current two things which I think might be causing the issue are either:
1) an old version of MDAC - they are using Win2k and I am on WinXP - mine works, theirs does not.
2) a registry setting defaults to have literally "<SERVERNAME>" in the field and over the phone I told them the IP address to put in there. I have a sneaking suspicion that they replaced the "SERVERNAME" with it, but left the brackets - an easy mistake and I didn't specifically warn them against this.

I have asked for them to email me a screenshot of that registry screen and will determine from there.

 

[esmithbda]

It turns out it was a simple matter of the Windows 2000 machines hae MDAC 2.5 and the XP ones had MDAC 2.8. The app was written with the assumption of MDAC 2.8, but no documentation of it.

Once they installed the newest MDAC, then it worked great.

 

[SQLBill]

Congrats for figuring that out....

-SQLBill

Posting advice: FAQ481-4875