The underlying connection was closed

If you ever get the above error message from a (SAP) webservice…this might fix it (at this in the generated proxy).

protected override System.Net.WebRequest GetWebRequest(Uri uri)
        {
            System.Net.HttpWebRequest webRequest = (System.Net.HttpWebRequest)base.GetWebRequest(uri);
            webRequest.KeepAlive = false;
            webRequest.PreAuthenticate = true;
            webRequest.ProtocolVersion = System.Net.HttpVersion.Version10;
            return webRequest;
        }

A severe error occured…SQL Server 2000

A few weeks ago we ran into the following error in a piece of code: 

System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded.

Wierd…the code was running fine until yesterday! After some investigation (CSI-style) we found that the “XP_CMDSHELL”-commando in our stored procedure was the guilty one.

Background Information
Quote: “When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.”

What’s causing?
In our case we were using the xp_sqlagent_proxy_account, which used a domain service account, which migrated to another domain, which wasn’t communicated, which…well, anyway.  We found the problem 🙂

Just this
Ow, just this snippet to set the proxy account:

use master
go

xp_sqlagent_proxy_account N’SET’
                        , N'<DOMAIN>’
                        , N'<PASSWORD>’
                        , N'<PASSWORD>’
And to read the current setting…

use master
go

xp_sqlagent_proxy_account N’GET’

In both cases, logon as sysadmin…