SQL Server across domains - can this be done

[scroce]

ok - I guess I'm just looking for a straight yes or no answer here. maybe with some elaborations if people are willing.

1. I'm kind of somewhat familiar with SQL Server 2000.
2. I'm decent at networking concepts but, by no means a whiz.

I'm in a small office with a peer to peer network. We are a remote office of a large company. We all log into a company wide domain called BIG domain over a T-1 connection. We have no servers at this location.

I have a database program that I've been creating in Access over the course of a couple of years. It's getting too big and 6 people in my office need regular access to it.

So I loaded w2k Server with SQL Server2k on an extra pc. This created a new domain called MYDOMAIN of which that PC is now a domain controller. I successfully imported and converted all my access tables to SQL Server on my new w2k Server.

Finally the question - I want to hook up my access front end to my data on my newly configured SQL Server. My front end is on my PC which is a member of the BIG domain.

Can I hook up my front end GUI with my back end Data, even though the two PC's are on different domains?

Right now, all the PC's are able to see each other just fine - so I am optimistic -

any input gladly taken

But I'm having trouble setting up the DSN. It keeps saying "general network error" when I test.









I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[mrdenny]

Yes, however a couple of things.

To use windows authincation you would need to setup a domain trust. Odds are your IT dept isn't going to let this happen.

Why not remove the MYDOMAIN you setup, and simply put your SQL Server on the BIG domain?

You can do it, if you set the SQL Server to TCP connections, and issue sql usernames and password to everyone.

Denny

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

 

[scroce]

bingo - mrdenny. you're right that they're probably not going to let that happen and that kind of red tape is exactly what I'm trying to avoid here.

But as far as I understand, can I can't just reconfigure my server to join the BIG domain, or can I? I don't have the passwords/authorization to join a new computer to the domain - which puts me back in having to deal with the corp IT. - you have to manually do this, don't you?

The idea was to try and keep it completely separate, but I guess I can't do that.

I was able to successfully map drives and share resources to my server on MYDOMAIN - shouldn't this mean that I can run a client/server application as well?

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[SQLBill]

You can have your front-end on one domain and the back-end on another domain. That's how my system is set up. SQL Server 2000 on domain1 and the users are on domain2. My users use Crystal Reports to access the data. The only thing is, the domains don't trust each other so the users have to have SQL Server logins.

Unfortunately, that means they have to see me when they need to change passwords.

I've been working on learning Access so I can create a form for my users to change their own passwords.

-SQLBill

 

[scroce]

Ok - so you're saying it can be done, but it has to use SQL server logins, not windows authentication - right?

SQLBill, at this point i'd really be overjoyed if I got as far as you have - since I don't have that many users, it wouldn't be that much of an issue to manage their passwords.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[SQLBill]

Here's quick steps if you don't need Windows Authentication.

1. In SQL Server, open Enterprise Manager and drill down until you see Security. Expand that and find Logins. Right click on logins and select New Login. Fill out the information on the General Tab, go to Database Access tab and give proper access to the database(s) your users are allowed to access.

2. On the front-end machine. Find the ODBC connection setup (under Admin tools or in Control Panel), set up an ODBC connection to the database.

-SQLBill

 

[scroce]

did everything you said, but having trouble connecting the front-end machine - for some reason the ODBC won't see the SQL Server

keep getting connection failed with a bunch of stuff that eventually ends up with "General Network Error" - "Check your network documentation"

With SQL Server Authentication is selected, and connect to SQL Server to obtain.... is selected with the user name and password that I set up on the server under Logins as you suggested.

I'm suspecting two things maybe.......

1. I seem to remember during setup it saying something about installing ADO instead of ODBC (or something like that) - is there a place you can go on the server to make sure ODBC is configured correctly?

2. I never really configured TCP/IP on my MYDOMAIN server. It just says 123.123.123.123 as the address, with a subnet of 255.255.0.0, which obviously does not match up with my client PCs that are part of the BIG domain - Even though the machines can still see each other, could this be messing it up?


I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[SQLBill]

First you need to make sure your machine can talk to the SQL Server maching.

Try PINGing the SQL Server. On your front-end machine open a command prompt window (Start>Run, type cmd and click ok). In the window, run PING <servername>. If that doesn't work try PING <ipaddress> (for example: PING mysqlservername or PING 123.123.123.123 - use the actual IP for the SQL Server).

If none of that works, something is keeping your machine from connecting to the server. It could be a firewall. Check what port SQL Server is listening on and make sure that port is open on the firewall. (They should be able to set the firewall to only allow certain ip's on certain ports).

-SQLBill

 

[scroce]

thanks bill - ok here's the response.

Can't ping the SQL Server via server name, nor 123.123.123.123

MYDOMAIN appears in mynetwork places, and I can open it and see the server name. If I double click the server, I can access all my shares.

I checked under Server Network Utility and Client network utility on my server, and they are both configured to prot 1433. I will have to check into if they are blocked at our corporate firewall -

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[scroce]

for now, I've done a workaround. I've successfully installed an instance of personal SQL server on one of my PC's already connected to the BIG domain.

Although it works, I now have a new problem for which I started a new thread - It's really SLOW!!!

so thank you all for your input, if any of you would like to further hone your troubleshooting skills, check out this thread:

thread962-805951

steve

I am a nobody, and nobody is perfect; therefore, I am perfect.