I need to allow a consultant to connect to SSIS on a SQL Server 2008 box without making him a local administrator. If I add him to the local administrators group, he can connect to SSIS just fine, but it seems that I can't grant him enough permissions through SQL Server to give him these rights without being a local admin.
我需要允许顾问在SQL Server 2008机器上连接到SSIS,而不必让他成为本地管理员。如果我将他添加到本地管理员组,他可以很好地连接到SSIS,但似乎我无法通过SQL Server授予他足够的权限,无需本地管理员即可授予他这些权限。
I've added him to every role on the server, every database role in MSDB shy of DBO, and he's still not able to connect. I don't see any SSIS-related Windows groups on the server - Is membership in the Local Administrators group really required to connect to the SSIS instance on a SQL Server? It seems like there is somewhere I should be able to grant "SSIS Admin" rights to a user (even if it's a Windows account and not a SQL account), but I can't find that place.
我已将他添加到服务器上的每个角色,MSDB中的每个数据库角色都不像DBO,他仍然无法连接。我没有在服务器上看到任何与SSIS相关的Windows组 - 本地管理员组中的成员资格是否真的需要连接到SQL Server上的SSIS实例?似乎在某个地方我应该能够向用户授予“SSIS Admin”权限(即使它是Windows帐户而不是SQL帐户),但我找不到那个地方。
UPDATE: I've found an MSDN article (See the section titled "Eliminating the 'Access if Denied' Error") that describes how to resolve problem, but even after following the steps, I'm still not able to connect. Just wanted to add it to the discussion
更新:我发现了一篇MSDN文章(参见标题为“消除'访问,如果被拒绝'错误”的部分),该文章描述了如何解决问题,但即使按照这些步骤,我仍然无法连接。只是想把它添加到讨论中
4 个解决方案
#1
When exactly does he get the "Access is Denied Error"? When trying to connect to SSIS using SSMS (SQL Server Management Studio) and specifying "SSIS" in the connection dialog? Or after this when trying to execute a package or something?
他什么时候得到“访问被拒绝错误”?尝试使用SSMS(SQL Server Management Studio)连接到SSIS并在连接对话框中指定“SSIS”时?或者在尝试执行包之后呢?
I guess it is the first situation and might be able to dig something up when I am back in the office tomorrow. I would let you know of course. Until then: where would your packages be stored if he could connect to the SSIS server: on the file system or in MSDB? If on the file system in the default location (under SQL Servers' root) or somewhere else? I think if you are not storing them in MSDB there are no SQL Server permissions involved here...
我想这是第一种情况,明天我回到办公室时可能会挖掘一些东西。当然我会告诉你的。在那之前:如果他可以连接到SSIS服务器,你的软件包将存储在哪里:在文件系统上还是在MSDB中?如果在默认位置(在SQL Server的根目录下)或其他位置的文件系统上?我想如果你没有将它们存储在MSDB中,那么这里不涉及SQL Server权限......
I was always able to work around this issue using the information provided in the article you mention.
我总是能够使用您提到的文章中提供的信息解决此问题。
Edit: Too bad; I cannot find anything "special" that we did but the steps mentioned in that MSDN article that you followed already.
编辑:太糟糕了;我找不到任何“特殊”,但我们已经遵循了MSDN文章中提到的步骤。
In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
在“启动权限”对话框中,添加或删除用户,并为相应的用户和组分配适当的权限。可用权限是本地启动,远程启动,本地激活和远程激活。启动权限授予或拒绝启动和停止服务的权限;激活权限授予或拒绝连接到服务的权限。
In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups.
在“访问权限”对话框中,添加或删除用户,并为相应的用户和组分配适当的权限。
Restart the Integration Services service.
重新启动Integration Services服务。
Connecting by using a Local Account
使用本地帐户连接
If you are working in a local Windows account on a client computer, you can connect to the Integration Services service on a remote computer only if a local account that has the same name and password and the appropriate rights exists on the remote computer.
如果您在客户端计算机上的本地Windows帐户中工作,则只有在远程计算机上存在具有相同名称和密码以及相应权限的本地帐户时,才能连接到远程计算机上的Integration Services服务。
#2
I know it´s an old question, but in my case I solved the problem following the MSDN Article that you mention. But in the first try, I didn´t add users to the local group Distributed COM, that was the key. This article is more clear in my opinion.
我知道这是一个老问题,但在我的情况下,我解决了你提到的MSDN文章之后的问题。但是在第一次尝试中,我没有将用户添加到本地组分布式COM,这是关键。在我看来,这篇文章更加清晰。
#3
Is he running the SSIS packages locally or remotely?
他是在本地还是远程运行SSIS包?
If he's running locally on his workstation, he should only need normal SQL priveledges (i.e. select / insert / whatever) on the relevant databases and tables he's accessing, as it's just a normal SQL connection.
如果他在他的工作站上本地运行,他应该只需要他正在访问的相关数据库和表上的正常SQL priveledges(即select / insert / whatever),因为它只是一个普通的SQL连接。
Or is he deploying packages to be executed remotely?
或者他是否正在部署要远程执行的程序包?
#4
I know that this is an old question, but I've run into this problem before and had success simply using a different version of SQL Management Studio. I think my problem might have been that the server was 2012 and I was trying to use 2014 to connect to SSIS.
我知道这是一个老问题,但我之前遇到过这个问题,只是使用不同版本的SQL Management Studio取得了成功。我认为我的问题可能是服务器是2012年,我试图使用2014连接到SSIS。
#1
When exactly does he get the "Access is Denied Error"? When trying to connect to SSIS using SSMS (SQL Server Management Studio) and specifying "SSIS" in the connection dialog? Or after this when trying to execute a package or something?
他什么时候得到“访问被拒绝错误”?尝试使用SSMS(SQL Server Management Studio)连接到SSIS并在连接对话框中指定“SSIS”时?或者在尝试执行包之后呢?
I guess it is the first situation and might be able to dig something up when I am back in the office tomorrow. I would let you know of course. Until then: where would your packages be stored if he could connect to the SSIS server: on the file system or in MSDB? If on the file system in the default location (under SQL Servers' root) or somewhere else? I think if you are not storing them in MSDB there are no SQL Server permissions involved here...
我想这是第一种情况,明天我回到办公室时可能会挖掘一些东西。当然我会告诉你的。在那之前:如果他可以连接到SSIS服务器,你的软件包将存储在哪里:在文件系统上还是在MSDB中?如果在默认位置(在SQL Server的根目录下)或其他位置的文件系统上?我想如果你没有将它们存储在MSDB中,那么这里不涉及SQL Server权限......
I was always able to work around this issue using the information provided in the article you mention.
我总是能够使用您提到的文章中提供的信息解决此问题。
Edit: Too bad; I cannot find anything "special" that we did but the steps mentioned in that MSDN article that you followed already.
编辑:太糟糕了;我找不到任何“特殊”,但我们已经遵循了MSDN文章中提到的步骤。
In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
在“启动权限”对话框中,添加或删除用户,并为相应的用户和组分配适当的权限。可用权限是本地启动,远程启动,本地激活和远程激活。启动权限授予或拒绝启动和停止服务的权限;激活权限授予或拒绝连接到服务的权限。
In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups.
在“访问权限”对话框中,添加或删除用户,并为相应的用户和组分配适当的权限。
Restart the Integration Services service.
重新启动Integration Services服务。
Connecting by using a Local Account
使用本地帐户连接
If you are working in a local Windows account on a client computer, you can connect to the Integration Services service on a remote computer only if a local account that has the same name and password and the appropriate rights exists on the remote computer.
如果您在客户端计算机上的本地Windows帐户中工作,则只有在远程计算机上存在具有相同名称和密码以及相应权限的本地帐户时,才能连接到远程计算机上的Integration Services服务。
#2
I know it´s an old question, but in my case I solved the problem following the MSDN Article that you mention. But in the first try, I didn´t add users to the local group Distributed COM, that was the key. This article is more clear in my opinion.
我知道这是一个老问题,但在我的情况下,我解决了你提到的MSDN文章之后的问题。但是在第一次尝试中,我没有将用户添加到本地组分布式COM,这是关键。在我看来,这篇文章更加清晰。
#3
Is he running the SSIS packages locally or remotely?
他是在本地还是远程运行SSIS包?
If he's running locally on his workstation, he should only need normal SQL priveledges (i.e. select / insert / whatever) on the relevant databases and tables he's accessing, as it's just a normal SQL connection.
如果他在他的工作站上本地运行,他应该只需要他正在访问的相关数据库和表上的正常SQL priveledges(即select / insert / whatever),因为它只是一个普通的SQL连接。
Or is he deploying packages to be executed remotely?
或者他是否正在部署要远程执行的程序包?
#4
I know that this is an old question, but I've run into this problem before and had success simply using a different version of SQL Management Studio. I think my problem might have been that the server was 2012 and I was trying to use 2014 to connect to SSIS.
我知道这是一个老问题,但我之前遇到过这个问题,只是使用不同版本的SQL Management Studio取得了成功。我认为我的问题可能是服务器是2012年,我试图使用2014连接到SSIS。