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.
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.