April 26, 2006 at 5:05 pm
Hi,
I remote to a SQL 2005 server/Windows 2003 and run certain SQL ETL process on that server (say Server A). I also need to link server to another SQL server (say Server B) from Server A by using different Windows account/password. How do I use sp_addlinkedserver to setup it up? Thanks.
Chris
April 27, 2006 at 8:52 am
Here is the example that shows how to set up Linked Server from 2005
Open Object Browser à Go to Server objects àGo to Linked Servers
Right Click Linked Server àClick New Server à General Tab
Linked Server LinkSQLSRVName à Security Tab àBe Made using this context
Remote login abcd2005 Remote password abcd2005
Server Options tab à Set all options true
Note: Create sql server account in LinkSQLSRVName server with name abcd2005
This account do not require any admin privilege or any user mapping.
Now go to sql2005
Open New Query Analyser and try following commands
select user_name()
Result should be dbo if it is not so following commands wan’t work.
Close that qry and open new query analyzer and try again , it will work.
SELECT * FROM [LinkSQLSRVName].NORTHWIND.DBO.CATEGORIES
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
April 27, 2006 at 4:16 pm
Hi Sameer,
I am getting following error:
OLE DB provider "SQLNCLI" for linked server "XXXXX" returned message "Communication link failure". Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'XXXX\yyyyy'.
Is this something to do with SQL connection setup? Thanks.
Chris
April 28, 2006 at 8:08 am
Chris,
I suggest you to make sure that SQL 2005 allow remote access and also
set right userid and password for linked server.
If that is not the case then look in Security Account Delegation.
For using Windows authentication you must enable the servers for
Account Delegation.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
June 14, 2006 at 7:24 am
check out http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 14, 2006 at 8:31 am
Did you setup your hosts file to point to the secondary server?
also, sqlserver2005 dosent have remote access setup by default. you have to enable it thru the configuration manager under network
Good luck
Eric
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply