May 22, 2008 at 12:02 pm
Hi All,
I have a SQL Server 2005 Standard Edition SP2 (9.00.3054.00) instance that is giving me a bit of a headache. This server (let's call it "SCHEDULE1") is primarily used for scheduled jobs via SSIS. There are no local user databases installed. It is running on a VMWare ESX virtual machine, and has Symantec AV 10.1.5 and Cisco Security Agent. I mention these things because the problem appears to be one of connectivity and / or authentication.
What's going on is that I cannot remotely connect to SSIS on SCHEDULE1 from any machine. I get the dreaded "Access is Denied" error. Well, any machine except one. I will get to that in a bit.
Trust me when I say I have followed all the recommended steps (and the SQL Server Central advice) to allow remote DCOM access, activation, and launch. I've added the desired domain group to the SCHEDULE1 DCOM Users group, and gone to the MSDTSServer object in Component Services on SCHEDULE1 to grant them access. I also granted the domain group a login to the SCHEDULE1 SQL instance and set its roles in MSDB as Public, db_DTSAdmin, sb_DTSLTUser, and db_DTSOperator. The domain group is also in the SCHEDULE1 administrators group.
I've even gone so far as to disable the SCHEDULE1 Windows firewall service, antivirus, and CSA agent. Still no joy.
Overkill, I know, but it's a frustrating problem. From my workstation, and from numerous other workstations and servers on the domain, users get the "Access is Denied" error (Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)). Whenever this error occurs, nothing appears in any log I've seen. I've even turned on verbose DCOM logging to see if I can identify the problem. Even with that enabled, there are no DCOM events in the logs of the SCHEDULE1 server.
However, there is one machine I've found so far that has no problem connecting to remote SSIS on the SCHEDULE1 server. This server (Let's call it "SILO1") is a SQL Server 2005 Enterprise (x64) SP2 instance. I've done a lot of comparison of settings and I can't see anything different in the way they are configured. Obviously I am missing something. Can anyone recommend a setting or property to check?
Here are my thoughts on what might be going on:
1. The access denied error is misleading. There may be a problem establishing a session with SSIS due to some kind of firewall activity. That's why the requesting machine detects the service is running, but isn't able to authenticate to it. SSIS depends on Windows credentials.
i. Furthermore, I've seen occasional USERENV 1006 LSASRV 40960 errors on the SCHEDULE1 machine:
1) Windows cannot bind to the domain (Invalid Credentials). Group Policy processing aborted.
2) The Security System detected an authentication error for the server LDAP/ . The failure code from authentication protocol Kerberos was "The attempted logon is invalid. This is either due to a bad username or authentication information. (0xc000006d)".
ii. No, the above errors don't coincide with any attempt to connect to remote SSIS. I am just concerned that the server might not be properly authenticating back to the DC. This isn't the only server on the domain having some kind of weirdness with Kerberos.
2. The Cisco Security Agent, even when turned to "low" or "off", may still be interfering with remote connections.
3. The instance may have some corruption or misconfiguration I haven't detected, and may need to be reinstalled.
Am I close?
May 23, 2008 at 7:47 am
A few steps and questions about this "problem"
1) are the users in the administrators group on the server running SSIS service
2) If not then DCOM settings need to be altered on a per user (or group) basis for the serivce
see http://www.sqlservercentral.com/Forums/Topic484638-148-1.aspx
and http://www.sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx
There are msdn articles also on this subject
May 23, 2008 at 12:24 pm
Jonathan Butler (5/23/2008)
A few steps and questions about this "problem"1) are the users in the administrators group on the server running SSIS service
2) If not then DCOM settings need to be altered on a per user (or group) basis for the serivce
see http://www.sqlservercentral.com/Forums/Topic484638-148-1.aspx
and http://www.sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx
There are msdn articles also on this subject
1. Yes. There is a group called "Analysts", of which I am a member. The "Analysts" group is a member of the local SCHEDULE1 Administrators group.
2. The Analysts group is also granted the following rights/membership:
a. Member of the local SCHEDULE1 Distributed COM users Group
b. Member of the local SCHEDULE1 SQLServer2005DTSUser$ group
3. The Distributed COM users group has full Activation/Launch/Access permissions for the MSDTSServer DCOM object.
4. The Analysts group is a member of all DTS roles in the MSDB instance on SCHEDULE1.
I know this is way more access than is required to grant simple remote SSIS access, but I'm trying to be methodical in what I open up, so I'll know where the problem is.
Keep in mind that I don't think it's strictly a permissions issue, because although I cannot access SSIS on "SCHEDULE1" from my workstation, I can access it from "SILO1".
I forgot to mention that this is using the SSMS method to connect to SSIS, which I don't know if it uses a different method than the BIDS. I also ran the SQL 2005 best practices analyzer and it gave me the same "Access Denied" error when attempting to connect.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply