How to create Linked MS SQL Server in Management Studio?

Databases

Creating MS SQL Linked Server means that you can access your remote MS SQL database (on other server) like it is local.

Syntax to access some data is as simple as that:

[linked server name].[database name].dbo.[table name]

Official Microsoft documentation is really sometimes hard to use. A lot of information but none practical example of usage.

microsoft-documentation-linked-server

Google search also didn’t helped much on the first try. After few tries I finally found out how you can add MS SQL Linked server to your existing database server.

So, you need first to add new linked server going to Server Objects > Linked Servers and then right click with mouse and choose New Linked Server… and you will end up with something like this (what to fill here is the real mistery):

addiing-linked-server

I gave up with this and I finally found the SQL command to do this properly:

EXEC master.dbo.sp_addlinkedserver
@server = N'YOURSERVER\SQL2008,1444', --You can include Your server, DB instance, port in not 1433
@srvproduct=N'MSSQL',
@provider=N'SQLNCLI',
@provstr=N'PROVIDER=SQLOLEDB;SERVER=YOURSERVER\SQL2008,1444'

--Now set the name of your linked server and access (we used 'Linked')
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Linked', @useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='YourP@@sw0rd'

That’s all folks 🙂