0

Till today i am using the linked server with the user of sql having same username and password in both the servers. For now on i can not keep the 2 servers password same for some reason and i still need the sql server to have linked connection.

When searched over net i found that i can achieve the linked server connection with the separate username and password using the 'sp_addlinkedsrvlogin' as mentioned in the article. In the same article at the end i found that storing the password for linked server is not secure and not recommended.

So my question is is there any secure way of storing the password for the linked server? because i need the linked server to connect with the different credentials as of the source server. Or is there any way that i can make the script ask for password every time i run the linked server query.

Note: i use the linked server from both .net coding and manually in sql management studio.

Update:

I am not using windows authentication to connect the linked server, i created single sql user which is common (username and password) in both the servers and running the query in that user for linked server connections.

  • 1
    That section in SQL Server documentation is very vague. I also have a feeling that some is plain incorrect, but that is just a guess from my side. I've asked around if we can clarify this and possibly have some clearer wording in that BOL section. Just wondering: Is Windows Authentication (both local and to the linked server) an option? That was you wouldn't have any stored passwords (hashed or not). – Tibor Karaszi Aug 8 '18 at 7:31
  • I didn't get you exactly @TiborKaraszi. If the 'Windows Authentication' is only the other option i can do that but i am not the only person who is using the linked server more than 20 person use this and managing all the windows authentication will be real headache. – IT researcher Aug 8 '18 at 8:04
  • 1
    To be able to decrypt a linked server password an intruder must have local administrator privileges on the server, so getting the password wouldn't be the most your trouble in this case. – Denis Rubashkin Aug 8 '18 at 8:52
  • 1
    Are the users currently using windows authentication for the local login? Then i don't see that much hustle to create those 20 windows logins on the remote machine and map these local logins for the linked server using "impersonation" (or whatever it is called in the GUI/sp). – Tibor Karaszi Aug 8 '18 at 9:17
  • 1
    @it-researcher, read Erik's answer in this topic: stackoverflow.com/questions/3731755/… – Denis Rubashkin Aug 8 '18 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.