Attempting to use an NT account name with SQL Server authentication

Question

The authentication mode is "Mixed" for my SQL Server 2016.

I'm using SSMS to run cross-server queries.

I've checked the user account & have ensured that the account has proper authority. I can use a different account and run cross-server queries as expected. The account has proper authority to the databases in question and it has authority to them. I've tried everything I've seen on the internet searches I've done and no luck. The account can login using SSMS.

My linked server properties are:

The account's login properties are:

Has anyone else seen this & have a resolution?

** EDIT: rebooting the target server fixed the issue

Answer 1

When creating a linked server you choose the authentication mechanism by which the remote connection will be made. If you select the fourth option (pictured), the remote login must be a Sql Server authenticated login. It cannot be a windows login.

The only way to connect through a linked server using windows authentication is to forward the credentials of the login on the local server. There is no option to specify a windows username and password.

Indeed, there is no way, ever, to specify a password when connecting to a Sql Server with windows credentials, since the whole point of windows credentials is that you're already authenticated. That happened when you logged in to windows in the morning^*

You can only (and must always) specify a password if you are using Sql Server authentication.

What seems to be going on in your case is that the linked server may have been created with the wrong security options. This is just easier to explain with an image:

---

^{* More precisely, a connection will be made using the account that the client is running under. If you start SSMS using a "runas /user ..." command, then the windows credentials used to connect to servers will be the credentials specified in runas}