March 4, 2015
I recently received this question:
Why am I getting an error ‘Invalid authorization specification’. I used my SA account, but i keep getting the error that the credentials specified are incorrect.
There is a very simple answer to this question, which I will share with you.
The error you get when the credentials are invalid:Linked server is a connection between two servers in your network. If you want to connect the servers, the specified credentials need to be known on both servers, So, unless you use the same password for sa acounts on all your network server, which is never recommended in the first place! I advise you to create a specific Linked server sql account, for example I created a LS_2014 account on both servers and specified on which databases the Linked server is allowed to read and select data, as you can see in the example below.
This keeps your databases secure and easier to manage.
You could also set it to use your current logged in account, but you need enough permissions on all servers and depending on who is using the SQL server, the linked server might not be available.
If you use the account specifically created for the Linked server and test the linked server account, the connection will be successful and you will see the table you granted read permissions on is available in the linked server dropdown.