linked server setup

[allware]

I have 2 sql servers that I need to link together.

Server 1 has the following details:
Windows Authentication Only
NT Domain (cannot upgrade to Active Directory at this time)
SQL Server 2000


Server 2 has the following details
Mixed Authentication
Active Directory
SQL Server 2000

I am need set the linked server up on Server 2 to link to Server 1.

Is my only option to change Server 1 to mixed mode for authentication?

Thank you,
Mike

 

[allware]

Just wanted to add a little more to the description to help make sure my question is as clear as possible.

User will log call stored
procedure on Server 2 being authentication with a SQL Server login, the
stored procedure needs to then make a call to Server 1 which is NT
Authentication only.

 

[mrdenny]

In that case, yes you will need to enable SQL Auth on Server 1.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

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

http://www.mrdenny.com

 

[schusser]

I have the same situation. What if the sp resides on server 1 and needs to call server 2?

Thanks in advance.

 

[allware]

I solved this situation myself a few days ago, sorry for not posting the answer sooner. I did not have to change my server to sql authentication.

Here is what I did.

I created a system DSN connection to my server(server1) on t remote server (server2).

I then changed server2 service account to run under a domain account with very restricted priveleges.

I added the domain account as a user to server1 and assigned it rights to the appropriated database and then explicitly denied all rights except for the views I created to simulate what my stored procedure was going to do. (I believe the same would have worked for stored procedure but did not test that)

Then back on Server2 I created my linked server using the DSN connection.

Everything works great now. If you click on the linked server in Enterprise Manager you will get errors, but if you run a query in Query Analzer everything works. The reason for Enterprise Manager giving errors is because I believe Enterprise Manager is trying to connect using the account loged onto the machine and not the service account.

I hope this helps others.

Mike

 

[allware]

Going from server1 to server 2 is much easier. You can just create a sql server login on server 2, give it the appropriate rights.

Then on server 1 create a linked server and then on the security tab select the option for be made using this security context and use the sql server login that you created on server 2.

Everything will work fine this way.

Mike