Create Linked Servers Dynamically

Question

Create Linked Servers Dynamically

gts31

Ten Centuries

Points: 1294
More actions
March 30, 2004 at 4:42 pm

#84063

I have a need to create linked servers on the fly. The problem is that I cannot use Windows authentication and cannot figure out a way to create the linked server and access the remote server without having a username and password as parameters for sp_addlinkedserver or sp_addlinkedserverlogin.
I have created an encrypted procedure that will create the link using a name and password. Problem is that in order to put this into production the script to create all of the necessary procedures and whatnot must be checked into a change management system and be deployed from there. This leaves the password in clear text and is a security risk that will not pass auditing.
The only option I can think of is to create a static link to and from each server where it is needed. What a major hassle if the password changes for this account or another database or server is added.
Is there any way to do this dynamically without a password?
Thanks,
George

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 1

One solution is to change this requirement...which is a wise one, except in this very limited circumstance. As the saying goes, "Hard Cases make Bad Law". This is a hard-case....and an exception to policy may be required.

"Problem is that in order to put this into production the script to create all of the necessary procedures and whatnot must be checked into a change management system and be deployed from there."

Bert De Haes Hall of Fame Points: 3078 More actions · Answer 2

You could store the login/password (encrypted) in a table and let the procedure fetch this from that table. You could also store this in the registry and read it from there using xp_regread

gts31 Ten Centuries Points: 1294 More actions · Answer 3

Grasshopper,

The requirement isn't going to change (as you said, for good reason). Was just wondering if I missed something. Thanks for your reply.

Bert,

The problem with keeping the password stored in a table encrypted is that I would be unable to unencrypt it. Therefore I wouldn't be able to actually use the password to add the remote login.

And as far as the registry, that would still leave the password stored in clear text somewhere, even it it might be difficult to find.

Thanks everyone. And if you come up with something that might work please feel free to reply!

Thanks, George

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 4

Remember encryption doesn't have to be one-way only....and also who's to say that XMHRTYDSDS is just plain text or HELLOWORLD encrypted? If you had XMHRTYDSDS and it was encrypted...unless somebody knows your encrype-decrpyt software/algorithm...they won't necessarily get HELLOWORLD out of it!!

A combination of nt security, sql security, vss security and an encrypt/decrypt tool should provide you with enough comfort to make it manageable.

Remember you seem to have an unachieveable task at the moment - unless you amend some constraints....primarily the storing/location of any userid/password.

Bert De Haes Hall of Fame Points: 3078 More actions · Answer 5

you can find procedures on this site to crypt/decrypt strings. Of course, you still have to pass the key ( password ) to crypt/decrypt this as a parameter to the procedure, otherwise crypting will not help.