• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 359
  • Last Modified:

the use of SQL link server

Dear all expertist,

Right now we plan to create link server for MS SQL server and we run this script:

/****** Object:  LinkedServer [TWDB]    Script Date: 03/13/2012 14:14:10 ******/
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'TWDB')EXEC master.dbo.sp_dropserver @server=N'TWDB', @droplogins='droplogins'
GO

/****** Object:  LinkedServer [TWDB]    Script Date: 03/13/2012 14:14:11 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'TWDB', @srvproduct=N'SQLCL10', @provider=N'SQLNCLI10', @datasrc=N'<IP address>,<port number>', @catalog=N'TRACKWORKDB'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TWDB',@useself=N'False',@locallogin=NULL,@rmtuser=N'capdiadmin',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'TWDB', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

-------------------------------------------------------------------------------------------------------

So the question is, anyway to make link server connect using server name instead of IP address?

DBA100.
0
marrowyung
Asked:
marrowyung
  • 3
1 Solution
 
marrowyungSenior Technical architecture (Data)Author Commented:
is it say that, if I script the link server out in SQL script and change the IP address of Sql name, we have to type in the password again?

DBA100.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
any way to script put the password also ? or we just, once script the link server object out, manually input login name and password in order to make it work?
0
 
jogosCommented:
Think about it. It wouldn't be a great security when scripting out a user the password is scripted with it automaticly.

When you put the password in the script file, see password is replaced by '####' again after use so it's not open readable on the filesystem (or trashbin).
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
jogos,

Yes, you are right and I am saying the same thing about that to my users, however, it only create one time for testing only and I should prepare for this kind of script for setup purpose.

So there are no way except manually do it ourselves?

DBA100.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now