0

We are trying to move away from some old habits of using the SA account for absolutely everything in SQL.

In doing so we had to change some linked server security context from SA to another SQL Server authenticated account with elevated privileges until we sort out what permissions are needed.

After doing so I now get an error when executing a stored procedure remotely:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.

I looked at select * from sys.sysservers and the linked server in question has isremote = 1

I created another linked server but used the fully qualified domain name so I can have two linked servers to the same server.

The new linked server has isremote = 0

When I run the same stored procedure referencing the new linked server it works.

I don't want to delete the old linked server because I have no clue what is using it.

My stored procedure is really basic, it only selects 1. Why would it be trying to login as SA on one and not the other when it no longer references it?

This server does have SQL Server Replication enabled and is version 2008 R2.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user17927782
  • 1
  • I want just one linked server without the crazy fully qualified domain name but am unsure of the ramifications to replication if that linked server is dropped and re-created. The setting for IsRemote can only be changed with a drop and re-create. It's driving me nuts that it still cares about SA even though there isn't a reference. – user17927782 Jan 13 '22 at 22:10
  • The old one is a "remote server" as opposed to the newer "linked server" setup. Apparently there are differences with RPC and distributed transactions. Remote servers are deprecated so I'd advise you to move to the new setup. You can use [this answer](https://stackoverflow.com/questions/11866079/changing-properties-of-a-linked-server-in-sql-server) to rename a server. I'd suggest you recompile any views or procedures that use it – Charlieface Jan 13 '22 at 22:11
  • 1
    Also, please be sure to use [`sys.servers`](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-servers-transact-sql). See the big note at the top of [the doc for `sys.sysservers`](https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysservers-transact-sql) - it's been deprecated since SQL Server 2005 and could provide misleading info. – Aaron Bertrand Jan 13 '22 at 22:17

0 Answers0