通过存储过程插入链接服务器上的表失败 [英] Insert into a table on a linked server through a stored procedure fails

问题描述

我需要在SQL Server 2008实例上执行存储过程，该实例将行插入到运行在单独服务器上的SQL Server 2016实例的表中。 
两台服务器都不在域中，我使用链接服务器连接到2016实例。 
我的测试设置使用在同一台服务器上运行的2个实例，因此行为不会完全相同。

I need to execute a stored procedure on an SQL Server 2008 instance that inserts rows into a table on an SQL Server 2016 instance running on a separate server.  Neither server is in a domain and I’m using a linked server to connect to the 2016 instance.  My test setup uses 2 instances running on the same server, so the behavior will not be exactly the same.

我创建了一个工作正常的存储过程当我以sa运行它时，但是一旦我在程序执行之前使用EXECUTE AS在不同的登录下进行测试，我就会开始出错。 
究竟是什么错误取决于我试图让它发挥作用的确切变化。 
我已尝试签署程序，在程序定义中使用WITH EXECUTE AS，以及其他一些我现在甚至都记不起来的事情。

I have created a stored procedure that works fine when I run it as sa, but as soon as I use EXECUTE AS before the procedure execution to test under a different login, I start getting errors.  Exactly what error depends on exactly what changes I make trying to get it to work.  I’ve tried signing the procedure, using WITH EXECUTE AS in procedure definition, and a few other things I can’t even remember right now.

I可以尝试服务经纪人，但我试图避免额外的复杂性。

I could try service broker, but I was trying to avoid the extra complexity.

我得到的当前错误是"拒绝访问远程服务器，因为当前的安全上下文不受信任。"

The current error I get is "Access to the remote server is denied because the current security context is not trusted."

可以告诉我是否我想要做的是什么，如果有的话，我可以用来开始的任何引用？

Can any tell me if what I am trying to do is possible, and if so, any references I can use to get started?

推荐答案

特定的错误消息你得到的是当你冒充数据库用户时，如果你将WITH EXECUTE AS添加到存储过程，那就是你正在做的事情。如果你这样做，你也会得到这样的信息：

The particular error message you get is when you impersonate a database user, and that is what you are doing if you add WITH EXECUTE AS to the stored procedure. You will also get this message if you do:

EXECUTE AS USER ='DOMAIN\someuser'

go

EXEC your_sp

go

REVERT

EXECUTE AS USER = 'DOMAIN\someuser'
go
EXEC your_sp
go
REVERT

当您模拟数据库用户时，您将被沙箱插入当前数据库，无法访问其外部的资源，例如链接服务器。

When you impersonate a database user, you are sandboxed into the current database and cannot access resources outside of it, for instance linked servers.

另一方面，如果你这样做：

On the other hand, if you do:

EXECUTE AS LOGIN ='DOMAIN\someuser'
go

EXEC your_sp

go

REVERT

EXECUTE AS LOGIN = 'DOMAIN\someuser'
go
EXEC your_sp
go
REVERT

里面没有限制服务器实例，结果取决于链接服务器的设置方式。如果使用登录映射设置链接服务器，以便DOMAIN\someuser映射到远程服务器上的SQL登录名，则上面的代码将工作。

There are no restrictions inside the server instance, and the outcome depends on how the linked server is set up. If the linked server is set up with login mapping so that DOMAIN\someuser maps to an SQL login on the remote server, the above will work find.

另一方面，如果链接服务器具有自映射，上述操作失败，因为SQL Server实例内的模拟在其外部无效。 （非常好的理由！）我相信错误信息与您发布的内容不同。
因此，如果您有自映射并且想要测试链接服务器是否适用于DOMAIN \ Somuser，您实际上必须以DOMAIN \ Someuser身份登录到本地SQL Server实例。 （例如，通过RUNAS启动SSMS。）

On the other hand, if the linked server has self-mapping, the above fails, because the impersonation inside an SQL Server instance is not valid outside of it. (For very good reasons!) I believe that the error message is different from what you posted, though. Thus, if you have self-mapping and want to test that the linked server works for DOMAIN\Somuser, you will actually have to log in to your local SQL Server instance as DOMAIN\Someuser. (For instance, by starting SSMS through RUNAS.)

这篇关于通过存储过程插入链接服务器上的表失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！