query

SQL Server DBA Tutorial 115-How to Create Linked server in SQL Server



Sharing buttons:

welcome to tech brothers in this video

we're going to learn how to create link

server in sequel server we'll be using

two ways to do it sequence every

management studio graphic user interface

and using T sequel script sometimes we

have no other choice but to create link

server the reason I'm saying that I'm

not big fan of link servers but if

you're using synonym and trying to

access the remote objects of another

sequel server then you can you have to

create the link server and also if you

are trying to if you're using your T

sequel if you're writing coded and T

sequel and you're trying to access the

remote resources of sequel server

such as tables to procedure you have to

create a link server you you cannot do

it without link server but my

recommended way that if you wanted to do

that please create SSIS packet because

there you don't have to create link

servers you can define your sources and

destination multiple times in

integration services but that's my

recommended way but just in case that if

you have to create a link server how to

create a link server I'm going to show

you in this video so we're going to go

ahead and this is my sequence ability

attack brother client is my server

sequel mirror is my instance name and in

order to create link server you need to

expand your instance and go to several

objects if you expand several objects

you will see a link several right here

if you have more links that we're

already it will appear right here but

we're going to in order to create link

server new link server we'll go ahead

and right click on link server click on

new link server now first thing that you

need to do is you need to name your link

server which I'm going to do is prod and

if you are there a couple ways you could

do that if you're connecting from this

sequel server instance to another sequel

server instance the best way to create a

link server is click on sequel server

right here and all these choices will be

gone

and you have to provide link Server

basic basically the name of the remote

server instance in my cases tech tech

brothers sequel back slash a sequel PR

OD this is my sequel server instance

another sequel server instance that is

basically installed on tech brothers

sequel server so I'm trying to access

all the databases or whatever the

database is I'm trying to access on this

server so I need to provide the fully

qualified the server name and the

instance name in my linked name server

so this is one way that you can do it do

that let's go ahead and do this first

and then we're going to go ahead and

basically do the other data sources

option so I'm going to go ahead and

click on skill D this is very very

important up here you have an option

local server login to remote a separate

login mapping if you wanted to if you

have permission of impersonate the

logins local logins with the remote

logins and remote login password you can

add it here and click on not be made and

that would access your remote sequel

server resources but we're going to do

the best way my recommended way is when

you create a link server please have a

sequence server of authentication user

and use that particular user right here

be made with using security context

because it's much more secure so in my

case I do have a sequel server remote

login to login this login needs to have

access on the remote sequel server so in

my case I have a DB manager and I'm

going to provide the password right here

so this is the password that this will

connect to the remote server so there

are some server options most of the time

you do not need to touch these server

options but if you need to change the

server options such as data access RPC

RPC out all these options are available

for you so we're going to go ahead and

click on general and click OK and it's

going to tell us that a tech brothers

sequel sequel PRD already exists and I'm

going to show you I just wanted to

reproduce this that if the sequel server

if you click on sequel server and that

sequel server already exists doesn't

matter what security context it is under

it's not going to let you create another

link server with the same name so if

we're if we cancel this and expand our

link server you can see TBS sequel

server sequel prod is already existed

and this database this sequel server

user that I use to connect with if you

go to the properties and go to the

security you will see exactly the same

thing

DB manager and the password and up here

I have clicked on sequel server and

given the name and server options I have

not touched any of them so just so that

you know that this is how you create it

so once you create that you can expand

this and click on catalogs this catalog

will depend on the permission of this

user right here whatever the access this

user have how many databases access of

this user has this will appear in

catalogs catalogs means the databases in

this context so we're going to go ahead

and cancel this this is my sad min on

the other my remote server so if I click

on catalog it should show me all the

databases that reside on sequel PR or

the instance in TBS sequel server so

we're going to go ahead and expand that

as you can see I can see all the

databases on that server so what I'm

going to do right now is leave this here

and we'll go

to go ahead and cover the other option

click on new on link server and I'm

going to name this link server PR OD and

we're going to use other data sources

let's look at the providers right here

is Microsoft oledb provider for sequel

server and other options we have a

Native Client 11 point oh and then we

have ODBC right here and we have

analysis services if you wanted to

connect your analysis services and you

wanted to create a link server to

analysis services you can use this if

you have an ODBC connection already made

which I'm going to show you real quick

up here these are the ODBC 32 and this

is ODBC our 64 if you have created any

one of them and you wanted to use that

in your link server you can go ahead and

select all a DB ODBC driver so we're

going to go ahead and keep the sequel

server user right here

Microsoft already be provided sequel

server per a product name if you eat

basically you can leave this alone the

data source is much more important data

source is network name of sequel server

that means you need to provide the

sequel server fully qualified a server

and instance name in my case is TBS

sequel backslash SQL PR OD so this is my

TBS sequel server this is my data source

and this is the string if you wanted to

basically create a string and put that

here which will cover this data source

and all that catalog and everything all

the information you can put that here

but I would just go ahead and put the

data source here if you know what

database this the user that you're going

to configure that you wanted to access

you can put right here I'm going to go

ahead and do sales orders I'm going to

go ahead and connect with sales orders

the wave the my permissions right now

is that I am sysadmin the user I'm using

to create this link server

I'm sis admin so it's going to give me

all the databases basically but there

would be a time when you have a user who

has permission to certain databases and

not all of the databases and sometimes

you have to program you can you have an

option to provide the database name

right here so catalog is your database

name so let's click on security I'm

going to go ahead and do the same thing

DB manager and provide the password and

I'm going to click OK and as you can see

that it has created right here another

link server so if we open that it should

show us all the databases the reason it

is showing us all the databases because

that it has a syst admin so let's go

ahead and take the sysadmin out and see

if we can just access the sales order

database so I'm going to go ahead and

connect with my sequel prod and I'm

going to go ahead and go into logins and

I'm going to go ahead and give you go to

user mapping and find sales order right

here is DB admin up there I'm going to

go to several roles and I'm going I'm

going to take the sysadmin out so this

user now has only access to sales order

so I'm going to disconnect this so we're

going to go ahead and refresh this and

click on the catalog as you can see now

it doesn't have all the permissions on

all the databases it has just the sales

orders so this is how basically you

create a link server using sequel server

management studio graphic user interface

and I'm going to go ahead and delete

these right here the

Lync servers and load our cqt sequel

script and create using T sequel script

so I'm going to go ahead and click on

file open and we're going to go ahead

and create links server

so up here this is the link server as

you can see I'm using DB manager and

providing the password these are the

server options right here this is just a

T sequel way to basically create a

sequel server I'm using basically the

sequel server option if you saw that I'm

not using the other provider in this

particular script but if you need to use

other provider you can use the similar

script but you need to put a product

name and all other options that's there

so we're going to go ahead and execute

this it says it it already exists and we

are not connected with the right

database so we're going to go ahead and

ctrl a ctrl C I'm going to copy it and

I'm going to go ahead and connect with

sequel mirror and let's make sure that

we don't have this link server we don't

and let's so we are connected with

sequel Mirror so we're going to go ahead

and execute this as you can see that

command completed successfully let's go

ahead and refresh that and as you can

see that us we are connected with sequel

prod and we should be able to access

the log-in failed I believe that just

because of this when you create using

script and you script it out already

created link server right here is the

the password is not will get reset when

you run the script so we're going to go

ahead this is for security reason this

is good

so we're going to go ahead right click

on this particular server click on

security and put the password just one

more time any time you move the link

server from one place to another place

and if you're using the sequel server

authenticated user then it's going to

put the password like this and all you

need to do is put the password in here

again

so alright we're going to go ahead and

refresh that and go to the catalog and

now we see that this particular user

have permission to sales order and we

can look at all the tables in sales

order so basically this is how you

create links server using sequel server

management graphic user interface and T

sequel script and we saw two options

using connecting directly to sequel

server and using other providers I hope

this video helps