SQL Protocols

Service Principal Name (SPN)

Did you know that beginning with SQL Server 2008, support for service principal names (SPNs) has been extended to enable mutual authentication across all protocols. Administators can now define their own SPNs. Thus SQL Server 2008 makes secure authentication more manageable and reliable by allowing clients to directly specify the SPN to use.

Fore more information, click here:

http://msdn2.microsoft.com/en-us/library/cc280459(SQL.100).aspx

----
Tres London
SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

TCP Provider: No connection could be made because the target machine actively refused it

In this post, I am trying to list out potential cause and general solution to this specific error when you are making connection to SQL Server 2005.

First, It is due to TCP connection fail, since you can tell from the title, "TCP Provider: .xx", which indicates that either your connection string explicitly use 'tcp:" prefix, say "Server=tcp:<machinename> ...." or your client protocol setting ask for first try Named Pipe connection, if fails, then fallback to TCP connection. For eg, in "Client NetworkUtility", you see the protocol order is "np on top of tcp". 

Secondly, this error is winsock error "WSAECONNREFUSED  - 10061". You'd better go through the following checklist to fix the "connection refused" problem.

1) Are you connecting to the right server? Check out whether your target machine name is valid.

2) Whether the <machinename> you specified in your connection string resolve to the correct ipaddress? do:

  ping <machinename> (if your remote server has ipv6 enabled, it might return ipv6 address). Go to the remote machine to see whether it returned the correct ipaddress.

 Then do ping -a <ipaddress>, can it be resolved to correct target machine name?

3) Are you connecting to default instance or named instance? Please go to the target machine, to check whether the instance started successfully, you can go to "Service Control Manager", find out "MSSQLSERVER" or "MSSQL$<instancename>" whether they are running?

4) Does your target SQL instance enabled TCP and listening on a valid TCP port?

a. If it is default instance, on your client machine, try : "telnet <ipaddress> 1433", remember, SQL Server 2005 default instance always use the reserved port 1433, namely, if you modify it to any other port number, the client app can not connect to the sql instance.

b. If it is named instance, on your target server machine, go to "SQL Server Configuration Manager", open "Protocols for MSSQL$<instancename>", check TCP enabled, and right click TCP, find out the tcp port number under 'IPAll". Then on your client machine, do " telnet <hostname> <portnumber>", see whether it succeeds.

5) Is your browser service running on the target server?

If you connect to named instance, then on your target server machine do "net start sqlbrowser".

6) Are your target server behind firewall?

If so, go to " SQL Server 2005 Surface Area configuration", click "Surface Area configuration for service and connection", then click the instance name, enable its remote connection.

You should be able to see the sql tcp port and udp port(1434 for browser service) is opened in Firewall, which enable you can telnet from client.

7) Does your client app set any alias that cause the connection redirect to an invalid port?

a. If you are using MDAC: on your client machine, click "cliconfig.exe", check out alias, whether it points to some other port that you can not telnet, if so, correct it or remove alias, since you should be able to connect through servername by sqlbrowser service automatcally respond to the correct port which server is listening on.

b. If you are using SNAC/SqlClient,  go to SQL Configuration Manager, check out client protocols, see alias setting.

8) Check out IPSec setting if you still face problem. See whether IPSec specification make dropping any packets.

9) If you still face problem, please use "Evenvwr" on the client machine, try to look up the remote server application log, see any info from SQL Server that indicates the sql instance reject client connection. Under this situation, it probably due to invalid client request. Open SQL Profile also can help you dig out which client data operation make server terminate the connection.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Login failed for user <xx>, Msg 18456, Level 14, State 1

This post focus on a typical cause of sql login failure issue described as the title.

In real world, a customer log on to the machine as a domain\user or local machine accout, he can connec to SQL Server through SQL Server Management Studio. However, he fails to connect by using SQL Login, namely, in connection string specify User as the NT account, and the password.

An example:

He did: "Create login [domain\user] from windows", then try -

osql -S -U [domain\user] -P xx or osql -S -U[machineaccount] -P xx.

To explain the reason, first you need to understand two Authentication Modes in SQL Server 2005:

• Windows authentication   Only authenticated windows users can gain access to the SQL instance. You need to add a windows login for each windows user or group that needs access to SQL istance. This is default and recommended mode because you can take advantage of all the centralized security policers of your Active Directory domain.
• Mixed Mode authentication Both windows login and SQL Server logins(neither of which are mapped to an operating system users) can access the SQL instances. You use mixed mode when you need to provide access to non-Windows-users for example when users of another operating system need access to SQL.

The –U and –P options are only for SQL Authentication. The SQL Client will not try to impersonate a windows user, instead it directly send the user/password to SQL Server and try to find a matching SQL login.

The solution is: You need to runas to impersonate the windows user and connecting using -E option in "osq.exe" or specify "Integrated Security=SSPI" or "Trusted_Connection = yes" in your connection string.

SSL Cipher Suites used with SQL Server

When enabling channel encryption between the application and SQL Server, users may wonder what encryption algorithm is being used to protect their data.  Unfortunately, this isn't an easy question to answer and here's why.

SQL Server (both 2005 and 2000) leverages the SChannel layer (the SSL/TLS layer provided by Windows) for facilitating encryption.  Furthermore, SQL Server will completely rely upon SChannel to determine the best encryption cipher suite to use.  Incidently, a cipher suite is a set of cryptographic algorithms that specifies the algorithm for key exchange, encryption, and message authentication (http://msdn2.microsoft.com/en-us/library/aa374757.aspx).  What the best cipher suite to use is negotiated by SSL/TLS and depends upon the cipher suites supported by the OS on the client and the server.  This negotiation is described in this white paper, http://download.microsoft.com/download/7/8/0/780b2238-1fc4-47f5-aa5b-def979ba558b/SSL%20Whitepaper.DOC, but simplistically it can be described as:

1. Determine the highest level protocol mutually supported by the client and the server.
   The currently recognised protocols are, from highest to lowest: TLS1.1, TLS1.0, SSL3.0, SSL2.0 
2. The client will provide the server with a list of its cipher suites from the negotiated protocol
3. The server will chose the strongest cipher suite that it is able to support from the client's list.

The following links list the cipher suites available for SSL2.0, SSL3.0, and TLS1.0:
http://msdn2.microsoft.com/en-us/library/aa380124.aspx
http://msdn2.microsoft.com/en-us/library/aa380512.aspx

Unfortunately, I have been unable to locate a reference providing a list of cipher sutes for each OS.  When I do find a reference, I will update this posting.

One last thing.  Some of you may be cuious to know if there is a way to influence the cipher suites that are used for channel encryption.  While there is nothing you can do at the SQL Server level, you can do something at the OS level as descrbed in this KB article, http://support.microsoft.com/kb/245030.  I don't expect that this is something that most of you will want or need to do.  If you're concerned about the cipher suites used because of FIPS compliance, then please refer to this article instead: http://support.microsoft.com/kb/920995.

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Install a self-signed test certificate that can be loaded by SQL Server automatically

In the blog Certificate for SQL Server 2005, I explained the requirements of a certificate for SQL Server 2005. Customer usually needs to purchase a certificate from certificate authorities (such as VeriSign), or apply a test certificate from some of the free providers available from Internet. The process is usually complicated and time-consuming. If you just want a certificate for testing purpose, there is an easy way to get one. You can use the makecert tool to generate a self-signed certificate for your own use. The following command would do the work:

You must replace YOUR_SERVER_FQDN with appropriate stuff. You can get the usage of makecert tool from http://msdn2.microsoft.com/en-us/library/bfsktky3(vs.71).aspx, makecert tool comes with winsdk and .Net SDK.

With this, your SQL Server will automatically load the certificate when the SQL Server restarts (if there are multiple certificates meets the requirements, SQL Server will load the first one it finds from the cert store.).

Note that, if you don’t have a certificate, SQL Server will automatically generate one self-signed certificate for you. However, the difference here is: the certificate generated by SQL Server will change every time the server restarts and the certificate’s subject CN is not your FQDN which is in general a critical part if client choose to authenticate the server. More specifically, if the client forces encryption, the connection attempt may fail because the subject CN does not match the server FQDN (in general) and the certificate is not trusted by the client. With the certificate we just generated, your connection attempt may also fail if your client forces encryption. You may get the following on your client machine:

C:\>osql -E -Syourserver

[SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.

[SQL Native Client]Client unable to establish connection

The reason of the failure is obvious, as stated in the error message. You can solve the issue by doing the following steps:

Connecting to SQL Server 2005 on Vista and Longhorn

Some customers have experienced problems connecting to SQL Server 2005 on Vista and Longhorn. Even though they are trying to connect using an account that is an administrator on the box, they get a "Login failed for user" error message with a state of 11.

The reason for this is that Windows Vista includes a new feature, User Account Control (UAC), which helps administrators manage their use of elevated privileges, and Windows Vista users that are members of BUILTIN\Administrators are not automatically added to the sysadmin fixed server role when they connect to SQL Server.

SQL Server 2005 SP2 provides an option during the setup process allows you to automatically add the user who is running setup into the SysAdmin Fixed Server Role. This option is turned off by default. To enable it, users can use the Setup UI by checking the checkbox in the Configuration Options page of the Setup UI with the caption 'Add user to the SQL Server Administrator role'. Alternatively, this can be done using the setup command line by specifying the ADDUSERASADMIN=1 option in order to add a login for the user running setup. To not add a login for that user, use ADDUSERASADMIN=0 (the default behavior).

This is discussed in detail at http://blogs.msdn.com/sqlexpress/archive/2006/11/15/sql-express-sp2-and-windows-vista-uac.aspx.

Users can always connect to SQL Server using elevated privileges (run as Administrator), or connect as sa (if Mixed authentication mode was selected during install). Once connected, any user can be granted login privileges or added to the sysadmin fixed server role by executing

EXEC sp_grantlogin 'domain\user'

or

EXEC sp_addsrvrolemember 'domain\user', 'sysadmin'

respectively.

Once this is done, users who have been granted login privileges or added to the sysadmin fixed server role no longer need to run as administrator in order to connect to SQL Server.

Stoyko Kostov, SQL Server Protocols

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server (Microsoft SQL Server, Error: XXX)

This error message is the most frequent error message when connecting to SQL Server. You see this error message when you use SqlClient. In SNAC, the error message is slightly differently as follows:

C:\>osql -E -Syourserver
[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [xxx].
[SQL Native Client]Login timeout expired

Basically, this error message just tell you that the client cannot make a connection to the server. It's equvalent to "SQL Server does not exist or access denied" in MDAC. Although the error message say about Named Pipe Provider, the issue does not have to be NP related. The reason is that, by default, the client stack try TCP and NP in order. If the connection attempt could not success with any of them, then NP is the last protocol tried and this is the error message to present to users.

When users see this error message, sometimes xxx is omitted. But actually, xxx is the most important part of this error message. xxx is Windows error code and it gives customer hints about why the connection fails. Here are some error code users often see. I also explain the root cause and possible solutions here.

1) xxx=53
winerr 53 means "The network path was not found". If you got this message, it means the client stack cannot find the target machine. Here are possible reasons for this failure,
 a) typo in the server name, or using "/" rather than "\" between server name and instance name, e.g. "myserver/myinst" is not correct.
 b) name resolution to the server name is not correct, "ping -a yourserver" would tell if that's the case
 c) The server machine is firewall'ed and file sharing is not in the exception list. You need put "File and Printer Sharing" in exception.

2) xxx = 1326
winerr 1326 means "Logon failure: unknown user name or bad password". When you get this error code, it means a) the client stack is able to reach the target machine, and b) the service account of the client application does not have enough credential to make a NP session to the server. NP is based on SMB (file sharing). The logon failure message represented by winerr 1326 is from SMB layer, not SQL Server. You need to make sure you can make a file sharing to the server machine with the same service account. If you cannot make a file sharing between your server and your client, NP in SQL Server would not success. Try "net use" or "Map Network Drive" to check this. This often happens when server and/or client is not on domain.

3) xxx = 2
winerr 2 means "The system cannot find the file specified". This error message means the client can reach the server but there is no Named Pipe listener with specific pipe name on the target machine. It also means the account can have a file sharing session without a problem.Possible reasons are:
 a) typo in instance name or wrong instance name. The instance name is not the one you are targeting. Note that for default instance, you shouldn't use MSSQLSERVER as instance name.
 b) Target SQL Server is not running
 c) Named Pipe is not enabled on the server. In this case, the SQL server is not listenning on the specific pipe name.

4) xxx = 233
winerr 233 means "No process is on the other end of the pipe". If you see this error, it usually means you don't have sufficient credential to connect to the server, e.g, wrong user name and/or password when you are using SQL authentication. The connection is closed by server before an error message is sent to the client.

There are other error code come with this error message, but they are not as often as the ones I just mentioned. When you see a new error code, just use "net helpmsg xxx" to get some information and see if you can figure out any suspecious issue.

As I have mentioned, this error message does not mean you have an issue with NP. You can do something unrelated to NP to eliminate this error message, e.g.
a) make sure target machine is accessible
b) target server is running
c) TCP protocol on the target instance is enabled
d) sqlservr.exe and/or the TCP port that the server listens is on firewall exception if firewall is ON.
By doing this, you may success with TCP or have a failure with an error message related to TCP and/or logon credential. This is because we success or fail with TCP protocol and does not even come to the point of using NP.

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

Users often see this error message when connection to a SQL Server and don't know where to start to solve the problem. In most forums, people says this is because remote connection is not enabled on the server. This is not exactly correct. Actually, this error message give customers very specific information and the solution is quite simple.

First of all, you get this error message only if you are trying to connect to a SQL Server named instance. For default instance, you never see this. Why? Because even if we failed at this stage (i.e. error locating server/instance specified), we will continue to try connect using default values, e.g defaul TCP port 1433, default pipe name for Named Pipes. You may see other error message due to failure later, but not this error message.

Every time client makes a connection to SQL Server named instance, we will send a SSRP UDP packet to the server machine UDP port 1434. We need this step to know configuration information of the SQL instance, e.g., protocols enabled, TCP port, pipe name etc. Without these information, client does know how to connect the server and it fails with this specified error message.

In a word, the reason that we get this error message is the client stack could not receive SSRP response UDP packet from SQL Browser. It's easy to isolate the issue. Here are the steps:
1) Make sure your server name is correct, e.g., no typo on the name.
2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
4) Make sure SQL Browser service is running on the server.
5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

Once you are done the steps, you should not see this error message anymore. You may still fail to connect your SQL server, but error message should be different and you have a different issue now. [Update: If it still fails, you may replace server\instance with tcp:server\instance and/or np:server\instance and see if it succeeds with either TCP or NP protocol. That way, you can isolate the issue a little bit. ]

There is one corner case where you may still fail after you checked step 1)-4). It happens when a) your server is a named instance on cluster or on a multi-homed machine, and b) your client is a Vista machine with Firewall on. I have explained the details at: Unable to connect to a SQL Server named instance on a cluster

[Update May 2009] My collegue found a good tool online which could be very helpful for users to isolate issues related to this error message. You can download PortQry from http://support.microsoft.com/kb/832919, run "portqry.exe -n yourservername -p UDP -e 1434". If this command returns information and it contains your target instance, then you can rule out possiblity 4) and 5) above, meaning you do have a SQL Browser running and your firewall does not block SQL Browser UDP packet. In this case, you can check other issue, e.g. wrong connection string.

As a final note, the error message for the same issue when you use SNAC is:
[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

If you still see this error, please post questions at MSDN SQL Server Data Access Forum:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1 
Please mention you already read this post and confirm steps you have taken and the results of each step.(e.g. server\instance,etc)  

Xinwei Hong, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

This error was most frequently hitted by our customers, and in this post, give a brief summary of troubleshooting tips for this specific error message.

First, take a look at below MSDN forum link lists about this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1287189&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1334187&SiteID=17

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1292357&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

The various causes fall into five categories:

1 Incorrect connection string, such as using SqlExpress.

2 NP was not enabled on the SQL instance.

3 Remote connection was not enabled.

4 Server not started, or point to not a real server in your connection string.

5 Other reasons such as incorrect security context.

Let's go throught the detail one by one:

I.   Incorrect connection string, such as using SqlExpress.

Check out: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=558456&SiteID=17

                https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1245564&SiteID=1

The typical error when dealing with Express includes:

a.  User is not aware of SqlExpress was installed as a named instance, consequently, in his/her connection string, he/she only specify ".","localhost" etc instead of ".\SqlExpress" or "<machinename>\Sqlexpress".

b. Np was disabld by default after installing SqlExpress.

c. If Sqlexpress was installed on the remote box, you need to enable remote connection for Express.

Please read the following blog for best practice of connecting to SqlExpress.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

II. NP was not enabled on the SQL instance.

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=136253&SiteID=1

Oppose to SQL 2000 which turn on all protocols, SQL 2005 SKUs turn off NP by default. So, when you see this error, please check:

1) Go to SQL Server Configuration Manager, See Server has NP enabled.

2) %windir%\program files\microsoft sql server\mssql.1\mssql\log, notepad ERRORLOG, see whether Server is listening on NP.  You should see "Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$<InstanceName>\sql\query]"

3) Notice that "sql\query" is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is "sql\query1", then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ], and go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

4) If you are using SQL Native Client ODBC/OLEDB provider({SQL Native Client} or SQLNCLI), go to SQL Configuration Manager, click client protocols, make sure NP and TCP are both enabled. Right click properties of NP, make sure client is using the same pipe name as server for connection.

5) If you are using MDAC ODBC/OLEDB({SQL Server} or SQLOLEDB) provider, in command line, launch "cliconfg.exe" and make sure NP enabled and right pipe name specified.

III. Remote connection was not enabled. 

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322792&SiteID=1

                 https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=763875&SiteID=1

If you are making a remote connection, namely, your target SQL Server is on the different box as client application, you might need to check whether:

a. "File and Printer Sharing" was opened in Firewall exception list.

b. Please see the blog for enabling remote connection for express and troubleshooting tips of remote connection.

http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx

http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

IV. Server not started, or point to not a real server in your connection string.

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=348662&SiteID=1

a. use "sc query mssqlserver" for default instance or "sc query mssql$<instancename>" to make sure SQL Server was started. Sometimes, reseason behind the broken of your client application w/ this error:40 might be SQL server restarted and failed, so, it'd better for you to double check.

b. User specified wrong server in their connection string, as described in the forum discussion, "MSSQLSERVER" is an invalid instance name. Remember, when you connect to default instance, <machinename> could be best representitive for the instance, when you connect to a named instance such as sqlexpress, you should specify <machinename>\<instancename> as data source in your connection string.

 V. Other reasons such as incorrect security context.

Check out: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=192622&SiteID=1

Such error also occured during user operation such as moving database or db mirroring or cluster, any DB OP that might invovle different sql instances, namely, the destination database is located in another sql instance and user is not aware of the state of the destination. I recommend you first isolate whether this fail is during connection stage or data operation stage.

a. During data operation, you are normally asked to type in the destination server name whether it is default to "(local)" or another server "<remotemachinename>". So, remember the exact string that represent the target instance, then when the error repros, open command line, use "sqlcmd -S<representitive> -E" ,see what happens, if the connection fail, please follow up above I - IV troubleshooting lists. otherwise continue.

b. If you can make basic conection, but still face the error, then there must be something that server reject the connection or client close the connection for some reason.

Summary, give checklist:

1. Is your target server started?

2. Is your target server listening on NP? Which Pipe?

3. Has your client enabled NP? Use the same pipe to connect as Server?

4. Are you making local connection? If so, what is the instance, default or remote?  

5. Did you put correct instance name in the connection string? Remember, Sqlexpress is a named instance.

6. Did you enable remote connection? Firewall? IPSec? "File and Printer Sharing" opened? Can access server?

7. Can you make basic connection by using <servername> or <servername>\<instancename>? Use sqlcmd or osql.

8. What is your repro step? What was your client APP doing during this error occuring? Which DB operation, detail?

MING LU

Building a WebMethod FORMAT=NONE Stored Procedure

Commonly for Web Services, the exposed interfaces (webmethods) must conform to some pre-defined contract.  To assist customers developing web services which conforms to these contracts, SQL Server 2005 Native Web Services provides the ability for customers to construct and format the output XML themselves.  To do this, the Stored Procedure (or CLR Stored Procedure) must return only one column of type 'nvarchar' (any length including "max") with the column name of "XML_F52E2B61-18A1-11d1-B105-00805F49916B".

For example:
CREATE PROC spTestFormat
AS
  DECLARE @x XML
  SET @x = N'<hello xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><!-- The main thing to remember when using this workaround is that there can only be one column, the column type must be "nvarchar" and the column name must be "XML_F52E2B61-18A1-11d1-B105-00805F49916B".--><foo><nested1><nullNode xsi:nil="true" /></nested1></foo><bar>You can specify any thing you like as long as it is valid XML.</bar></hello>'
  SELECT convert(nvarchar(max), @x) as 'XML_F52E2B61-18A1-11d1-B105-00805F49916B'
GO

When exposing the Stored Procedure as a WebMethod on the endpoint, remember to set the FORMAT keyword to the value of "NONE".

For example:
CREATE ENDPOINT epTestFormat
  STATE=STARTED
AS HTTP (
  ...
)
FOR SOAP
(
  WEBMETHOD 'http://tempuri.org'.'testFormat' (name='master.dbo.spTestFormat', FORMAT=NONE)
)

The resulting SQL Server output response for this Web Method will be:
<?xml version="1.0" encoding="utf-8"?>
<hello xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <!-- The main thing to remember when using this workaround is that there can only be one column, the column type must be "nvarchar" and the column name must be "XML_F52E2B61-18A1-11d1-B105-00805F49916B".-->
  <foo>
    <nested1>
      <nullNode xsi:nil="true" />
    </nested1>
  </foo>
  <bar>You can specify any thing you like as long as it is valid XML.</bar>
</hello>

The combination of this functionality of customizing the Stored Procedure result format with the Custom WSDL generation capability (see http://blogs.msdn.com/sql_protocols/archive/2006/11/07/building-t-sql-custom-wsdl-generator.aspx), users will be able to develop web services that fully describe the exact interface and to conform to any interface the organization defines.

Jimmy Wu, SQL Server Protocols
Disclaimer: This posting is provided “AS IS” with no warranties, and confers no rights

TDSSNIClient initialization failed with error 0x50, status code 0x50

In this post, I want to talk about a special cause of this specific error message when you try to start SQL 2005 instance.

The symptom here is: The NP and TCP protocol were disabled in your SQL 2005 default instance, and you tried to start it but failed. you went to Server ERRORLOG, and found following error message:

Server local connection provider failed to listen on [ \\.\pipe\sql\query ]. Error: 0x50
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x50, status code 0x50
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x50, status code 0x1.

0x50 stands for "The file has already exists". And the problem here is SQL 2005 was trying to listening on the default pipe name for legacy client locally connecting to the default instance under situation that server named pipe was disabled by default after fresh installation, however, the pipe name was occupied by another bogus server. In this case, I want to point out you might need to check whether there is a SQL 2K named instance on the same box, and it is running. You can verify that by running "osql.exe /Snp:. /E /Q"select @@version" " or "osql.exe /S\\.\pipe\sql\query /E /Q"select @@version" " to see whether it is the SQL 2K instance took over the pipe.

If so, you need to go to "SQL Server Network Utility", click the properties of Named Pipe, and check whether the pipe name was gone, or double check registry key "PipeName" under "\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<2kinstancename>", make sure there is correct value, then restart the 2K named instance, and restart the 2K5 default instance.

Note: You might hit another problem "0x5, ACCESS_DENIED" if you retry starting SQL 2k5 default instance after fixing the pipe name, which might due to if you shutdown the 2K instance and the handle of the pipe is still hold by kernel file system. Follow up the solution in the below blog:http://blogs.msdn.com/sql_protocols/archive/2006/03/09/546655.aspx.

MING LU

Connection Alias

SQL Server client stack has a facility to let user define connection aliases. Connection Alias can usually help user make connection eaiser, faster and with more convenience. However, if it's not used properly, it sometimes results in connectivity issues which might be difficult to isolate.

User can take advantage of connection alias in both MDAC and SNAC. The information is saved in registry. There are two tools can be used to define alias. One is SQL Server Connection Manager shipped with SQL Server 2005. The other one is SQL Server Client Network Utility shipped with Windows and can be found
at C:\WINDOWS\system32\cliconfg.exe.

All connection alias information can be found under the following registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Basically, alias can be used to:
1) define a easy-to-remember (or use) name of a real SQL Server name.
2) redirect SQL connection with SQL Server to another SQL Server instance on any machine.
3) make connection to SQL Server using a specific protocol (e.g. TCP, Named Pipe, etc) with specific parameter (e.g. TCP port, pipe name).

Case 3) is probably the most frequent usage. Because an alias always has a pre-defined protocol, it can speed up your connection. When making new connections, SQL server client try various protocols (Shared Memory, TCP, Named Pipe, VIA, etc) in a sequence. There is a pre-defined default protocols sequence, but user can change the sequence using the tools mentioned above. SQL Server Connection Manager is for SNAC and SQL Server Client Network Utility is for MDAC. If a connection fails with one protocol, we may try connection with another protocols if there are still time for login. So, if a user knows which protocol the server is listening on and they want to used it, he can define alias to use that protocol directly, saving the
time to try and fail with other protocols. Also, he can chose a protocol which has better performance on his system.

Two use cases:
a) A user can make connection to SQL Server named instance without having to specify the instance name in applications. For example, you have a named SQL Server instance NewInst on machine MyHost. You can define MyHostAlias to use Named Pipe protocol with the following Pipe name \\MyHost\pipe\MSSQL$NewInst\sql\query,

b) A user wants to use TCP protocol and has already configured the SQL Server to listen a specific port (e.g. 3456) rather than the default port 1433. Then, he can define a alias to do so. User can also pecify IP address directly in alias, thus saving some time on DNS query.

In the above cases, since the TCP port and/or Pipe Name is known and have been saved with alias, user does not have to rely on SQL Browser to discovery the server, which saves time and exposes less security risk as well.

As you can see, alias has a lot of benefits. However, if not used properly, you may see issues hard to trouble-shoot. For example, you defined an alias called MyHost and let it connect to a SQL Server on MyHost using TCP. Later, your DBA decides to turn off TCP and let the server only listen on NP or change TCP port number, you may not be able to connect to MyHost unless you remember an alias is defined and delete/redefine the alias. It's usually very hard for a user to realize the alias is the root cause of the issue.

So, as a suggestion, you'd better always put some special words in your alias so that you know it's an alias at anytime.  

Xinwei Hong, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

"Cannot Generate SSPI Context" error message, Poisoned DNS

Incorrect DNS can lead to various network connectivity issues. In this post, I explain how it affects your connection to SQL Server. In some cases, you may see the well-known "Cannot Generate SSPI Context" error message.

Suppose your SQL Server is located on a machine called HostA. Its IP address is A.B.C.D and its FQDN is HostA.mydomain.com. However, your DNS is poisoned and wrong results are returned to your DNS query. Then, you may have problems to connect to your server. There are two forms of DNS poison.

Case 1:

HostA --> A1.B1.C1.D1, i.e, clients get a wrong IP address for HostA or HostA.mydomain.com

In this case, a client may fail to find the server when connecting to the server using the hostname and/or FQDN. If it happens that another server is listening on A1.B1.C1.D1 on another machine, you may see more confusing error messages, usually, logon will fail. This situation is not very difficult to identify and the reason is obvious.

Case 2:

HostA --> A.B.c.D, i.e. clients can get correct IP address for HostA or HostA.mydomain.com 
A.B.C.D --> HostB.mydomain.com, i.e. The IP address is poisoned.

In this case, client can locate the server correctly and make a connection to the server. However, under the hood, SQL Server client stack will do a reverse lookup and build up a SPN based on the result. In this case, the client get something like this: MSSQLSvc/HostB.mydomain.com:1433
If such a SPN happens exist on the network (more specifically Active Directory), then the client will try to connect to the server using Kerberos. However, the service account of the target SQL Server is not the correct container of the SPN and Kerberos will fail. Clients will see the "Cannot Generate SSPI Context" error message. Imagining the SQL Server on the HostB is turned on and off periodically, the client will see occasional logon failure on the SSPI error message. Note that all these only happen when TCP is used to connect to the server.

For Case 2, if the client is on the same machine with the server (i.e. local connection), it should be OK unless the poisoned DNS coming from Host file. The reason is that, for local connection,  NTLM is usually used. There is one special situation you may see a different logon failure. If the server is a SQL cluster and the cluster IP address is poisoned, the connection from the local machine will fail with the following error message:
Login failed for user ''. Reason: Not associated with a trusted SQL Server connection.

One source for DNS poison is that user put an entry in hosts file (c:\WINDOWS\system32\drivers\etc\hosts) on the client machine and forgot to remove it when they do not need it anymore. Over the time, this entry may not match rhe dynamic record on DNS server and user will see connectivity issue.

Another good source I can think of: Nowadays, more people use laptop on corp network. Most people don't shutdown their laptop when they take the laptop off the network. This will leave the DNS entry on the DNS server for the specific IP address the laptop was using. Sometime later, another machine jumps in and obtained that IP address and ofcourse will register its own DNS entry for the IP. Then that IP will have two entries on the network. Actually, there might be more records (e.g. SPN) left on the networks if the machine did not properly logoff. All this can cause connectivity issues.

Here are the suggestions on trouble-shooting SQL connectivity issue related to DNS. (Actually, I suggest you always do the following check up the ensure the issue is not because of poisoned DNS)
Under command windows, run the following commands:
1) ping -a HostA  (and HostA.mydomain.com)
2) ping -a A.B.C.D  (If this only returns HostA, but not FQDN, usually means no DNS entry was found, and HostA is from Netbios)
3) nslookup
          >hostA.mydomain.com
          >hostA.mydomain.com
          >A.B.C.D
          >A.B.C.D
You need input FQDN and IP address at least twice in nslookup as you may see different results if there are more than one entry for the IP or FQDN.
If you find any mismatch between server's IP and FQDN, that could be the cause of your connectivity issue.

Please read more posts on this blog for related info:

Understanding Kerberos and NTLM authentication in SQL Server Connections
“Cannot generate SSPI context” error message, when connect to local SQL Server outside domain
“Cannot Generate SSPI Context” error message, more comments for SQL Server
Using Kerberos with SQL Server

Xinwei Hong, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Understanding Kerberos and NTLM authentication in SQL Server Connections

In this post, I focus on how NTLM and Kerberos are applied when connecting to SQL Server 2005 and try to explain the design behavor behind several common issues that customers frequently hit.

On this page:

Kerberos VS NTLM.

Requirements for Kerberos and NTLM in SQL Connections.

When are Kerberos and NTLM are applied when connecting to SQL Server 2005.

Common issues and workaround.

Troubleshooting Tips checklist.

I. Kerberos VS NTLM

NTLM Authentication: Challenge- Response mechanism.

In the NTLM protocol, the client sends the user name to the server; the server generates and sends a challenge to the client; the client encrypts that challenge using the user’s password; and the client sends a response to the server.If it is a local user account, server validate user's response by looking into the Security Account Manager; if domain user account, server forward the response to domain controller for validating and retrive group policy of the user account, then construct an access token and establish a session for the use.

Kerberos authentication: Trust-Third-Party Scheme.

Kerberos authentication provides a mechanism for mutual authentication between a client and a server on an open network.The three heads of Kerberos comprise the Key Distribution Center (KDC), the client user and the server with the desired service to access. The KDC is installed as part of the domain controller and performs two service functions: the Authentication Service (AS) and the Ticket-Granting Service (TGS). When the client user log on to the network, it request a Ticket Grant Ticket(TGT) from the AS in the user's domain; then when client want to access the network resources, it presents the TGT, an authenticator and Server Principal Name(SPN) of the target server, contact the TGS in the service account domain to retrive a session ticket for future communication w/ the network service, once the target server validate the authenticator, it create an access token for the client user.

II. Requirements for Kerberos and NTLM authentication

Kerberos, several aspects needed:

1) Client and Server must join a domain, and the trusted third party exists; if client and server are in different domain, these two domains must be configured as two-way trust.

2) Registered SPN. Service Principal Name(SPNs) are unique identifiers for services running on servers. Each service  that will use Kerberos authentication needs to have an SPN set for it so that clients can identify the service on the network. It is registered in Active Directory under either a computer account or a user account.

   Service Principal Name

   An SPN for SQL Server is composed of the following elements:    

   • ServiceClass: This identifies the general class of service. This is always MSSQLSvc for SQL Server. 
   • Host: This is the fully qualified domain name DNS of the computer that is running SQL Server. 
   • Port: This is the port number that the service is listening on.  

     eg:  MSSQLSvc/myserver.corp.mycomany.com:1433

NTLM

NTLM requires user's password to formulate a challenge-response and the client are able to prove its identities without sending the password to server. Thus you can tell if your client running under System Context w/o credential, what might happen?

NTLM fallback

NT LAN Manager is the authentication protocol used in Windows NT and in Windows 2000 work group environments. Windows Server 2003, Windows XP, and Windows 2000 use an algorithm called Negotiate (SPNEGO) to negotiate which authentication protocol is used. Although the Kerberos protocol is the default, if the default fails, Negotiate will try NTLM.

III. When are Kerbers and NTLM applied when connect to SQL Server 2005.

Under condition that you are using Integrated Security or trusted connection which use windows authentication.

1) Kerberos is used when making remote connection over TCP/IP if SPN presents.

2) Kerberos is used when making local tcp connection on XP if SPN presents.

3) NTLM is used when making local connection on WIN 2K3.

4) NTLM is used over NP connection.

5) NTLM is used over TCP connection if not found SPN.

To undersand these scenarios, first you need to know hwo to verify your SQL Server SPN exists:

download the SetSpn.exe from http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&DisplayLang=en

At a command prompt, type:

setspn -L <Account>

Account could be either <machinename> or <domainusername>

a. If your SQL Server running under LocalSystem or NetworkService account, you should be able to

see SPN by:

setspn -L <hostserver that your sql installed>

b. If your SQL Server running under a domain user account, you should be able to see SPN by:

setspn -L <mydomain>\<username>

c.If the domain user is non-admin, you can ask your domain administrator to register the SPN under

your account if you must use Kerberos authentication.

setspn -A <mydomain>\<username>

d. If your sql server is running under a local machine admin account, you can either ask your

domain administrator or run setspn under your domain credential to add the SPN.

Summary, SQL Server would automatically register SPN during start up if:

a. Your sql server running under LocalSystem/Network Service/Domain admin user account.

b. TCP/IP protocol is enabled.

Otherwise, you need to manually register SPN if forcing Kerberos authentication.

Normally, if you are making TCP connection, SQL driver on the client tries to resolve the fully qulified DNS name of the server that is running SQL, and then format the SQL specific SPN, present it to SPNEGO, later SPNEGO would choose NTLM/Kerberos depends on whether it can validate the SPN in KDC, the behavior is different from OS to OS, in most case, if SPN was not found, Kerberos authentication failed, it fallback to NTLM, but there is exception like in above case 2), if Kerberos authentication failed, it would not fallback. If you are making NP connection, SQL driver generate blank SPN and force NTLM authentication.

IV. Common issues and Workaround.

[1]  "Login Failed for user 'NT Authority\ANONYMOUS' LOGON"

In this scenario, client make tcp connection, and it is most likely running under LocalSystem account, and there is no SPN registered for SQL instance, hence, NTLM is used, however, LocalSystem account inherits from System Context instead of a true user-based context, thus, failed as 'ANONYMOUS LOGON'. See http://support.microsoft.com/kb/132679.

The workaround here is

a. ask your domain administrator to manually register SPN if your SQL Server running under a domain user account.

b. use NP connection.

c. change your sql server to run under either localsystem account or networkservice account.

Here, a is recommended.

[2] "Login Failed for user ' ', the user is not associated with a trusted SQL Server connection".

In this scenario, client may make tcp connetion, plus, running under local admin or non-admin machine account, no matter SPN is registered or not, the client credential is obviously not recognized by SQL Server.

The workaround here is:

Create the same account as the one on the client machine with same password on the target SQL Server machine, and grant appropriate permission to the account.

Let's explain in more detail:

When you create the same NT account (let's call it usr1) on both
workstations, you essentially connect and impersonate the local account of
the connecting station. I.e when you connect from station1 to station2,
you're being authenticated via the station2's account. So, if you set the
startup account for SQL Server (let's assume it's running on station2) to be
station2's usr1, when you connect to SQL from station1 with station1's usr1
login, SQL will authenticate you as station2's usr1.

Now, within SQL, you can definitely access station1's resources. Though, how
much access will depend on station1's usr1 permission.

So far, SQL only deal with an user who is part of the sysadmin role within
SQL Server. To allow other users (non-sysamdin) access to network resources,
you will have to set the proxy account. Take a look at the article for
additional info.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

[3] "Could not open a connection to SQL Server[1326]"

The same root cause as [2], just is making np connection.

[4] "Login failed for user '<domain>\<machinename>$' "

In this scenario, you client probably running under LocalSystem account or NetworkService account, so, just need to grant login to the account "domain\machinename$" in SQL Server.

[5] "Login failed for user 'NT Authority\NetworkService'"

This is a typical authorization failed case, and it probably when client running ASP.NET application and use ASPNET account or network service account.

workaround, see  http://support.microsoft.com/kb/316989/

[6] Can not generate SSPI Context.

This is typical Kerberos authentication failure, there are various situations that can trigger this error. see blog: http://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

The major reason is due to the Credential Cache( is used by Kerberos to store authentication information, namely the TGT and session ticked is cached so that can be used during their lifetime.)

The most general workaround is: clean up credential cache by using "klist.exe -purge" or kerbtray.exe or just reboot machine.

See more detail about various cause and solution in http://support.microsoft.com/kb/811889.

 Differenciate Authentication failed and Authorization failed.

When you saw error " Login failed for user ' ' ...." or " Login failed for user '(null)' " or " ANONMOUS LOGON", these are authentication failure.

When you saw error like " Login failed for user '<username>' ", these are authorization failure, which is related to your SQL server security settings.

The final part gives troubleshootin tips checklist for authentication fail which is the focus of this blog.

V. Troubleshooting Tips checklist

[1] Verify computer settings

http://technet2.microsoft.com/WindowsServer/en/library/e1c3f70d-f8b3-4642-93c6-61421fd1292e1033.mspx?mfr=true

[2] Verify DNS name resolution

The key factor that makes Kerberos authentication successful is the valid DNS functionality on the network.

ping <remoteserver> , ipaddress should return

ping -a <ipaddress> , FQDN should return

nslookup, type the ipaddress, should get FQDN, or type FQDN should return ipaddress.

[3] Verify NTLM works.

try command:

"net view \\server", or "net view \\ipaddress".

[4] Verify SPN set

See which account SQL Server is running under, if SQL Server fails to register SPN, there is errorinfo in ERRORLOG, but you should doublecheck whether expected SPN was manually registered by other people.

[5] Clean up your client credential cache and retry see whether the problem persists.

[6] Then go to part III, to see your scenario falls into which case listed, and analyze whether the problem is included in the Common issues part IV, and applied the solution. Again, be careful to differenciate authentication error and authorization error. If you face authorization error, recommend post your question to the security forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

[7] Make sure your SQL Server Protocol setting is correct for NTLM and Kerberos before go to step [8].

a. You are using windows authentication.

b. You already grant proper permission to the windows account,

c. Your server has SPN registered or not as you expected, also the port in SPN is the one that sql server is listening.

d. If making remote connection, you enabled "File and Printer Sharing" in the firewall on your remote server.

e. TCP/IP or NP is enabled.

f. Your client connection string specify the correct target server name and sql instance name.

[8] If you find it is pure Kerberos or NTLM issue, you need to check system log and  security log or even do netmon to gather Kerberos or NTLM error code for further debugging. 

See "Diagnose Tool" secion in this: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx#E2HAC

Summary:

If you face problem that did not list out in this post, please provide following info w/ your problem:

1) Which account your client is running under?

2) Which account your SQL Server is running under?

3) Is SPN registered for your SQL Server?

4) Does your client and server join the domain? Are they in the same domain?

5) Which OS your client and server is on?

6) What is the error message?

7) What error info in your SQL Server ERRORLOG?

8) What is your connection string?

9) Local connection or remote?

10) Linked server involved?

11) Any Kerberos delegation involved? double-hop or single-hop?

If your scenario invovle linked server and kerberos delegation, please check blog:

http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

MING LU

SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Building T-SQL Custom WSDL generator

We've gotten feedback from customers who have asked if it is possible to write a custom WSDL generator in T-SQL.  For those customers who do not wish to enable SQL CLR support, the following sample T-SQL SP can be used as a starting point for generating your own custom WSDL.  Please be advised that this sample is just as that, a sample.  It is NOT production level code and is provided as a technical demonstration that it is possible.

NOTE: The server response format for a SP can not be changed, unless you specify FORMAT = NONE on the ENDPOINT WEBMETHOD keyword syntax.  At which point, you are responsible to control the exact response.

This post will discuss just the WSDL portion.  A seperate posting later on will discuss how to customize SP response formats.  Please note that this post contains the entire T-SQL SP code and as such is a long posting.  The sample code is below:

CREATE PROCEDURE SpHttpGenerateWsdl
    @EndpointID int,
    @IsSSL bit,
    @Host nvarchar(128),
    @QueryString nvarchar(128),
    @UserAgent nvarchar(128)
as
begin
  set nocount on
  declare @http int
  set @http = 1
  declare @soap int
  set @soap = 1
  declare @started int
  set @started = 0
  declare @outputWSDL nvarchar(max)
  
  -- define the set of preset strings needed in the WSDL document
  set @outputWSDL = N'<wsdl:definitions xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"'
  declare @wsdlStartTypes nvarchar(50)
  set @wsdlStartTypes = N'<wsdl:types>'
  declare @wsdlEndTypes nvarchar(20)
  set @wsdlEndTypes = N'</wsdl:types>'
  declare @xsdStartSchema nvarchar(150)
  set @xsdStartSchema = N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace='
  declare @xsdEndSchema nvarchar(20)
  set @xsdEndSchema = N'</xsd:schema>'
  declare @xsdStartElement nvarchar (50)
  set @xsdStartElement = N'<xsd:element name='
  declare @xsdEndElement nvarchar(20)
  set @xsdEndElement = N'</xsd:element>'
  declare @xsdStartComplexType nvarchar(50)
  set @xsdStartComplexType = N'<xsd:complexType><xsd:sequence>'
  declare @xsdEndComplexType nvarchar(50)
  set @xsdEndComplexType = N'</xsd:sequence></xsd:complexType>'
  declare @wsdlStartMessage nvarchar(100)
  set @wsdlStartMessage = N'<wsdl:message name='
  declare @wsdlEndMessage nvarchar(20)
  set @wsdlEndMessage = N'</wsdl:message>'
  declare @wsdlStartPart nvarchar(50)
  set @wsdlStartPart = N'<wsdl:part name="parameters" element='
  declare @wsdlEndPart nvarchar(20)
  set @wsdlEndPart = N'</wsdl:part>'
  declare @wsdlStartPortType nvarchar(50)
  set @wsdlStartPortType = N'<wsdl:portType name='
  declare @wsdlEndPortType nvarchar(20)
  set @wsdlEndPortType = N'</wsdl:portType>'
  declare @wsdlStartOperation nvarchar(50)
  set @wsdlStartOperation = N'<wsdl:operation name='
  declare @wsdlEndOperation nvarchar(20)
  set @wsdlEndOperation = N'</wsdl:operation>'
  declare @wsdlStartInput nvarchar(50)
  set @wsdlStartInput = N'<wsdl:input name='
  declare @wsdlEndInput nvarchar(20)
  set @wsdlEndInput = N'</wsdl:input>'
  declare @wsdlStartOutput nvarchar(50)
  set @wsdlStartOutput = N'<wsdl:output name='
  declare @wsdlEndOutput nvarchar(20)
  set @wsdlEndOutput = N'</wsdl:output>'
  declare @wsdlStartBinding nvarchar(50)
  set @wsdlStartBinding = N'<wsdl:binding name='
  declare @wsdlEndBinding nvarchar(20)
  set @wsdlEndBinding = N'</wsdl:binding>'
  declare @soapBinding nvarchar(100)
  set @soapBinding = N'<soap:binding transport="http://schemas.xmlsoap.org/soap/http" style="document"/>'
  declare @soapStartOperation nvarchar(50)
  set @soapStartOperation = N'<soap:operation soapAction='
  declare @soapEndOperation nvarchar(20)
  set @soapEndOperation = N' style="document" />'
  declare @soapBody nvarchar(50)
  set @soapBody = N'<soap:body use="literal" />'
  declare @wsdlStartService nvarchar(50)
  set @wsdlStartService = N'<wsdl:service name='
  declare @wsdlEndService nvarchar(20)
  set @wsdlEndService = N'</wsdl:service>'
  declare @wsdlStartPort nvarchar(50)
  set @wsdlStartPort = N'<wsdl:port name='
  declare @wsdlEndPort nvarchar(20)
  set @wsdlEndPort = N'</wsdl:port>'
  declare @soapStartAddress nvarchar(50)
  set @soapStartAddress = N'<soap:address location='
  declare @soapEndAddress nvarchar(20)
  set @soapEndAddress = N'</soap:address>'
  declare @wsdlEndDefinitions nvarchar(20)
  set @wsdlEndDefinitions = N'</wsdl:definitions>'
  
  -- some local variables
  declare @endpointWsdl nvarchar(100)
  declare @endpointProtocol int
  declare @endpointType int
  declare @endpointState int
  declare @endpointBatches bit
  declare @endpointMethodCount int

  -- make sure WSDL is enabled on the endpoint
  select @endpointWsdl = wsdl_generator_procedure from sys.soap_endpoints where endpoint_id = @EndpointID
  if (NOT (LEN(@endpointWsdl) > 0))
  begin
    raiserror ('WSDL generation is disabled for this endpoint.', 16, 1)
  end
  -- make sure the query string is requesting for WSDL
  if (N'WSDL' <> UPPER(@QueryString))
  begin
    raiserror ('Unsupported Action, please double check value of query string.', 16, 1)
  end
  -- make sure the endpoint actually exists
  if ((select endpoint_id from sys.endpoints where endpoint_id = @EndpointID) is NULL)
  begin
     raiserror ('Specified Endpoint is invalid.', 16, 1)
  end
  -- make sure the endpoint is a SOAP endpoint and is started
  select @endpointProtocol=protocol,
            @endpointType=type,
            @endpointState=state
            from sys.endpoints where endpoint_id = @EndpointID
 
  if ((@endpointProtocol <> @http) OR (@endpointType <> @soap) OR (@endpointState <> @started))
  begin
    RAISERROR('Specified Endpoint is not a SOAP endpoint or is not started', 16, 1)
  end
  -- query to see if SqlBatch is enabled on the endpoint
  select @endpointBatches = is_sql_language_enabled from sys.soap_endpoints where endpoint_id = @EndpointID
  -- check the number of web methods specified on the endpoint
  select @endpointMethodCount = count(*) from sys.endpoint_webmethods where endpoint_id = @EndpointID
  -- if SqlBatch is enabled or if there is at least one web method, then generate WSDL
  if ((@endpointBatches = 1) OR (@endpointMethodCount > 0))
  begin
    -- Note: this sample does not actually general the definition for the SqlBatch method
    -- create a temp table to store the list of webmethods on the endpoint
    create table #tempWSDLMethod (db nvarchar(20) NOT NULL, 
                                  oOwner nvarchar(20) NOT NULL, 
                                  oName nvarchar(50) NOT NULL)
    insert #tempWSDLMethod (db, oOwner, oName)
         select LEFT(object_name, CHARINDEX(N'.', object_name)-1), 
             SUBSTRING(object_name, CHARINDEX(N'.', object_name)+1, CHARINDEX(N'.', object_name, CHARINDEX(N'.', object_name)+1)-CHARINDEX(N'.', object_name)-1),
             RIGHT(object_name, LEN(object_name)-CHARINDEX(N'.', object_name, CHARINDEX(N'.', object_name)+1))
         from sys.endpoint_webmethods where endpoint_id = @EndpointID
    -- create a temp table to keep track of all the information needed to generate WSDL
    create table #tempWSDLTable (id int identity primary key, 
                                 webMethodNamespace nvarchar(max) NOT NULL, 
                                 webMethodName nvarchar(max) NOT NULL,
                                 paramName nvarchar(100) NOT NULL,
                                 namespaceSuffix int DEFAULT 1)
    -- insert appropriate info to temp table
    declare webMethodDb_Cursor CURSOR FOR
        SELECT distinct db from #tempWSDLMethod
    declare @tDbName nvarchar(50)
    open webMethodDb_Cursor
    FETCH NEXT FROM webMethodDb_Cursor INTO @tDbName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        declare @tQuery nvarchar(max)
        set @tQuery = N'use ' + @tDbName + '; insert #tempWSDLTable (webMethodNamespace, webMethodName, paramName)
            select a.namespace, a.method_alias, RIGHT(b.name, (LEN(b.name)-1))
            from sys.endpoint_webmethods as a, sys.parameters as b
            where a.endpoint_id = ' + CAST(@EndpointID as nvarchar(10))
            + N' and b.object_id = object_id(a.object_name) and (LEN(b.name) > 0)' 
        exec (@tQuery)
        FETCH NEXT FROM webMethodDb_Cursor INTO @tDbName
    END
    CLOSE webMethodDb_Cursor
    DEALLOCATE webMethodDb_Cursor
    -- generate the WSDL document
    select @outputWSDL = @outputWSDL + N' xmlns:tns="' + default_namespace + 
          '" targetNamespace="' + default_namespace + '"'
          from sys.soap_endpoints where endpoint_id = @EndpointID
    declare webMethodNS_Cursor SCROLL CURSOR FOR
         SELECT distinct webMethodNamespace from #tempWSDLTable
    declare @tCount int
    declare @tMethodNS varchar(50)
    set @tCount = 1
    open webMethodNS_Cursor
    FETCH NEXT FROM webMethodNS_Cursor INTO @tMethodNS
    WHILE @@FETCH_STATUS = 0
    BEGIN
        select @outputWSDL = @outputWSDL + N' xmlns:s' + CAST(@tCount as nvarchar(3)) + N'="' + @tMethodNS + N'"'
        update #tempWSDLTable set namespaceSuffix = @tCount where webMethodNamespace = @tMethodNS
        set @tCount = @tCount + 1
        FETCH NEXT FROM webMethodNS_Cursor INTO @tMethodNS
    END
    select @outputWSDL = @outputWSDL + N'>'
    -- start the <wsdl:types> node
    select @outputWSDL = @outputWSDL + @wsdlStartTypes
    -- add any xsd:schema as necessary here
    -- one possibility is to store these XML schemas in a table and query the appropriate ones here
    -- and add them to the WSDL
    -- loop through the set of webmethod namespaces to add the appropriate xsd schema definitions
    FETCH FIRST FROM webMethodNS_Cursor INTO @tMethodNS
    WHILE @@FETCH_STATUS = 0
    BEGIN
        select @outputWSDL = @outputWSDL + @xsdStartSchema + N'"' + @tMethodNS + N'">'
        declare webMethodInfo_Cursor CURSOR FOR
        SELECT DISTINCT webMethodName, paramName 
            from #tempWSDLTable 
            where webMethodNamespace = @tMethodNS
            order by webMethodName ASC
        declare @tMethodName nvarchar(50)
        declare @tMethodNameBak nvarchar(50)
        declare @tMethodParamName nvarchar(20)
        declare @bFirstTime bit
        set @tMethodNameBak = N''
        set @bFirstTime = 1
        open webMethodInfo_Cursor
        FETCH NEXT FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodParamName
        WHILE @@FETCH_STATUS = 0
        BEGIN
           if ((NOT (@bFirstTime = 1) AND (@tMethodNameBak <> @tMethodName)))
           begin
               -- close out the method name node
               select @outputWSDL = @outputWSDL + @xsdEndComplexType
               select @outputWSDL = @outputWSDL + @xsdEndElement
               -- response message structure
               select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodNameBak + N'Response">'
               select @outputWSDL = @outputWSDL + @xsdStartComplexType
               select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodNameBak + N'Result">'
               select @outputWSDL = @outputWSDL + @xsdEndElement
               select @outputWSDL = @outputWSDL + @xsdEndComplexType
               select @outputWSDL = @outputWSDL + @xsdEndElement
           end
           -- request message structure
           if (@tMethodNameBak <> @tMethodName)
           begin
              -- add the method name node
              select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodName + N'">'
              select @outputWSDL = @outputWSDL + @xsdStartComplexType
           end
           
           -- add the parameters
           -- Make sure the appropriate parameter type is specified here
           -- This sample leaves it as xsd:anyType which is normally handled as an Object
           select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodParamName + N'">'
           select @outputWSDL = @outputWSDL + @xsdEndElement
           set @bFirstTime = 0
           set @tMethodNameBak = @tMethodName
           FETCH NEXT FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodParamName
        END
        -- close out the method name node
        select @outputWSDL = @outputWSDL + @xsdEndComplexType
        select @outputWSDL = @outputWSDL + @xsdEndElement
        -- response message structure
        select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodNameBak + N'Response">'
        select @outputWSDL = @outputWSDL + @xsdStartComplexType
        select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodNameBak + N'Result">'
        select @outputWSDL = @outputWSDL + @xsdEndElement
        select @outputWSDL = @outputWSDL + @xsdEndComplexType
        select @outputWSDL = @outputWSDL + @xsdEndElement
        CLOSE webMethodInfo_Cursor
        DEALLOCATE webMethodInfo_Cursor
        select @outputWSDL = @outputWSDL + @xsdEndSchema
        FETCH NEXT FROM webMethodNS_Cursor INTO @tMethodNS
     END
        
    CLOSE webMethodNS_Cursor
    DEALLOCATE webMethodNS_Cursor
    
    -- close the <wsdl:types> node
    select @outputWSDL = @outputWSDL + @wsdlEndTypes
    -- need to loop through each webmethod on the endpoint
    declare webMethodInfo_Cursor SCROLL CURSOR FOR
        SELECT DISTINCT webMethodName, webMethodNamespace, namespaceSuffix 
        from #tempWSDLTable
        ORDER BY webMethodNamespace ASC, webMethodName ASC
    declare @tIdSuffix int
    
    open webMethodInfo_Cursor
    FETCH FIRST FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- the <wsdl:message> node
        select @outputWSDL = @outputWSDL + @wsdlStartMessage + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapIn">'
        select @outputWSDL = @outputWSDL + @wsdlStartPart + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N':' + @tMethodName + N'" />'
        select @outputWSDL = @outputWSDL + @wsdlEndMessage
        select @outputWSDL = @outputWSDL + @wsdlStartMessage + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapOut">'
        select @outputWSDL = @outputWSDL + @wsdlStartPart + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N':' + @tMethodName + N'Response" />'
        select @outputWSDL = @outputWSDL + @wsdlEndMessage
        FETCH Next FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
    END
    -- the <wsdl:portType> node
    select @outputWSDL = @outputWSDL + @wsdlStartPortType + N'"' + name + N'Soap">'
        from sys.http_endpoints where endpoint_id = @EndpointId
    FETCH FIRST FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- the <wsdl:Operation> node
        select @outputWSDL = @outputWSDL + @wsdlStartOperation + N'"' + @tMethodName + N'">'
        select @outputWSDL = @outputWSDL + @wsdlStartInput + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + 
                             N'SoapIn" message="tns:s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapIn" />'
        select @outputWSDL = @outputWSDL + @wsdlStartOutput + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName +
                             N'SoapOut" message="tns:s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapOut" />'
        select @outputWSDL = @outputWSDL + @wsdlEndOperation
        FETCH NEXT FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
    END
    select @outputWSDL = @outputWSDL + @wsdlEndPortType
    -- the <wsdl:binding> node
    select @outputWSDL = @outputWSDL + @wsdlStartBinding + N'"' + name + N'Soap" type="tns:' + name + N'Soap">'
        from sys.http_endpoints where endpoint_id = @EndpointID
    select @outputWSDL = @outputWSDL + @soapBinding
    FETCH FIRST FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
    WHILE @@FETCH_STATUS = 0
    BEGIN
        select @outputWSDL = @outputWSDL + @wsdlStartOperation + N'"' + @tMethodName + N'">'
        select @outputWSDL = @outputWSDL + @soapStartOperation + N'"' + @tMethodNS + @tMethodName + N'"' + @soapEndOperation
        select @outputWSDL = @outputWSDL + @wsdlStartInput + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapIn">'
        select @outputWSDL = @outputWSDL + @soapBody + @wsdlEndInput
        select @outputWSDL = @outputWSDL + @wsdlStartOutput + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapOut">'
        select @outputWSDL = @outputWSDL + @soapBody + @wsdlEndOutput + @wsdlEndOperation
        FETCH NEXT FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
    END
    CLOSE webMethodInfo_Cursor
    DEALLOCATE webMethodInfo_Cursor
    select @outputWSDL = @outputWSDL + @wsdlEndBinding
    select @outputWSDL = @outputWSDL + @wsdlStartService + N'"' + name + N'">'
        from sys.http_endpoints where endpoint_id = @EndpointID
    select @outputWSDL = @outputWSDL + @wsdlStartPort + N'"' + name + N'" binding="tns:' + name + N'Soap">'
        from sys.http_endpoints where endpoint_id = @EndpointID
    if (@IsSSL = 1)
    begin
        select @outputWSDL = @outputWSDL + @soapStartAddress + N'"https://' + @Host + N'/' + url_path + N'" />'
            from sys.http_endpoints where endpoint_id = @EndpointID
    end
    else
    begin
        select @outputWSDL = @outputWSDL + @soapStartAddress + N'"http://' + @Host + N'/' + url_path + N'" />'
            from sys.http_endpoints where endpoint_id = @EndpointID
    end    
    select @outputWSDL = @outputWSDL + @wsdlEndPort + @wsdlEndService + @wsdlEndDefinitions
  end
  -- The WSDL document must be returned to the client using this GUID as the column name.
  select @outputWSDL as N'XML_F52E2B61-18A1-11d1-B105-00805F49916B'
end
go

Jimmy Wu, SQL Server Protocols
Disclaimer: This posting is provided “AS IS” with no warranties, and confers no rights