Ed Leighton-Dick's questions

We have run into a strange situation in which a SQL Server 2008 single-node cluster hangs. As background, we are rebuilding a Windows Server 2003/SQL Server 2005 two-node cluster using Windows 2008 and SQL Server 2008. Here's the timeline:

• Evicted the passive node (server B) from the Windows 2003/SQL 2005 cluster. The active node now functions as a single-node cluster with no problems.
• Wiped server B's disks and installed Windows 2008 and SQL Server 2008 as a single-node cluster. Since we do not want to the two clusters to communicate yet, we left the cluster's private network "heartbeat" adapter unconfigured. The cluster comes up and functions normally.
• Moved all databases to the new cluster. Cluster continues to function normally.
• Turned off server A (old cluster) in preparation for rebuilding as the second node of the new cluster.
• SQL Server instance on server B (new cluster) locks up, even though it should have no knowledge of or interaction with server A.
• Restarted server A. SQL Server instance on server B (new cluster) immediately begins working again.

Things we have tried:

• The new cluster's name responds to ping and NETBIOS requests, even while the SQL Server is hung.
• We have confirmed that no IP address is assigned to the old heartbeat adapter, and it is not pulling an IP address from DHCP.
• Disabling the heartbeat's network card has the same effect.
• No errors were generated in any logs - Windows or SQL.
• When the error first occurred, it sat in the hung state for quite some time (well over 10 minutes) before anyone figured out what was going on. This would seem to eliminate any sort of normal cluster timeout in which it would have been searching for the other node (even if one had been configured).

Server B is running Windows 2008 SP2, fully patched, and SQL Server 2008 SP1 CU7 (10.0.2775).

One of our in-house applications has a SQLCLR assembly which is currently experiencing problems. The developer is having problems recreating the issue using the version that is stored in our source control system, so he suspects that some code may have been released that was not uploaded to source control. Is there a way to extract a SQLCLR assembly into a .dll file so that he can reverse engineer it for analysis?

One of our admins noticed a strange pattern of login messages in the Windows event log on one of our SQL Servers, and after some investigation, I've found that it the same pattern can be found on most (but not all) of our SQL Servers. I cannot seem to find the source, however.

The pattern:

• A series of four messages related to Kerberos authentication occurs every 29 minutes on affected servers. (No, that's not a typo.)
• The series is event IDs 552, 540, 576, and 538 (in that order) on Windows 2003 and earlier. On Windows 2008, the series is 4648, 4624, 4672, and 4634.
• The first message in the series says that the SQL Server's service account is authenticating via Kerberos using my credentials.
• The second and third messages relate to a login using my credentials via Kerberos and the permissions granted.
• The fourth message is a logoff.
• The series always happens in under 1 second.

I've eliminated everything I can think of:

• No other messages in the Windows event log correlate to the messages.
• No messages in the SQL Server logs correlate to the messages.
• No SQL Server Agent jobs run on that scheduler.
• Task Scheduler has no jobs on any of the affected servers.
• Our 3rd-party job scheduler does not have any jobs that run on that schedule, nor does it have my credentials.
• I briefly suspected a 3rd-party monitor that we use, but the messages were produced even when the monitor was completely turned off.
• No other 3rd-party monitors are used that would have either the service account's credentials or my credentials.
• Our linked servers use SQL Server authentication, not Windows authentication.
• The servers are a mix of SQL Server 2000, 2005, and 2008, so it can't be related to any newer SQL Server technologies, such as Service Broker.

Does anyone have any suggestions of anything else to check to find the cause of these messages?

End users in my company frequently use Microsoft Query (through Excel) to perform ad-hoc queries of selected SQL Server databases. As we move to SQL Server 2005 on the backend, we are discovering that MS Query lists all objects from all schemas, regardless of whether the user has permissions to those objects or schemas (they don't). I have also tried using the newer Native Client drivers (both 9.0 and 10.0) but received the same results.

I have found that the users can select the schema they want when they select the connection in Microsoft Query, but if possible, I'd like to hide the unnecessary system objects and schemas to prevent confusion. Has anyone discovered a way to do this?

Our developers need to be able to start a SQL Server Agent job from their .Net code. I know I can call msdb..sp_start_job to do just that, but I don't want to give general user accounts direct access to run jobs.

What I'd like to do is to create a stored procedure in the application's database using the WITH EXECUTE AS clause to impersonate a proxy account. The procedure as we have it is:

CREATE PROCEDURE dbo.StartAgentJob 
    WITH EXECUTE AS 'agentProxy'
AS
BEGIN
    EXEC msdb.dbo.sp_start_job N'RunThisJob';
END

When we run this, though, we get the following message:

The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

Any ideas? Is this even the best way to do this in SQL2005?