SQL Server Q&A

Symptoms
The purpose of this article is to help you in the setup or in the troubleshooting of a configuration for a COM+ Application Server or a configuration for an Internet Information Server (IIS) computer that interacts with a clustered server that uses Microsoft Cluster Server (MSCS) that is behind a firewall.
You may have one of the following scenarios: COM+ or IIS computer (client computer)SQL Server clustered that uses MSCSMicrosoft Distributed Transaction Coordinator (MSDTC) as a clustered resource in its own resource group (own name and IP address)Cluster and client computer that are separated by a firewall Certain Internet Protocol (IP) addresses and their corresponding network names must be known by the client computer for MSDTC to work correctly. The client computer can resolve the following names and the following IP addresses by using Domain Name System (DNS), hosts file, or another name resolution method: MSDTC resourceAn instance of SQL Server if the cluster configuration is either active-passive or active-activeCluster Name Certain IP addresses and their corresponding network names must be known by the nodes in the cluster for MSDTC to work correctly. Both nodes in the cluster can resolve the client computer name to an IP address by using DNS, hosts file, or another name resolution method.
Resolution
Additionally, the firewall must be configured to allow bidirectional traffic to occur between the client computer and the cluster. The firewall rules must include the following: The IP network names and the addresses of both physical nodes on the clusterThe SQL Server Instances network names and addressThe client network name and addressesThe child network name and IP resource of the MSDTC Resource Firewall rules must include the range of IP ports that are defined in the registry to allow traffic. See the “References” section.
You may also have to open an additional range of available IP ports as a requirement for the cluster server. See the “References” section for more information.

Symptoms
If you try to connect a clustered named instance of SQL Server through a firewall, and you use only the instance name (for example,SQL_Virtual_Name\Instance_Name) in the connection string, the connection fails and you may receive one of the following error messages:
Error message 1

Specified SQL server not found.
Error message 2

SQL Server does not exist or access denied.If you run a network trace on the server, you can see that the server actually receives the query from the client computer on UDP port 1434 for the virtual server IP address; however, the answer is sent with the physical IP address of the cluster node that is currently running SQL Server.
Resolution
When a client computer connects to a computer that is running a clustered instance SQL Server, if the connection string does not specify the destination TCP port, the client library queries the server on port UDP 1434 to collect information about the instance.
When the server returns the information, the network frame contains the IP address of the physical node instead of the IP address of the virtual server. Depending on the firewall configuration, this network packet may be dropped, and the client may not receive any answer.

Symptoms
In Microsoft SQL Server 2005 or in Microsoft SQL Server 2008, you have a stored procedure that has a parameter whose name contains full-width underscore characters (＿). You create a replication on this stored procedure. However, when you run the snapshot agent, you receive the following error message:

Error: 14151, Severity: 18, State: 1.
Replication-Replication Snapshot Subsystem: agent <AgentName> failed. The replication agent had encountered an exception.
Source: Unknown
Exception Type: Microsoft.SqlServer.Management.Smo.WrongPropertyValueException
Exception Message: The Name property cannot have a value of @param＿name for this version of SQL Server.
Resolution
The snapshot agent calls the Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateStoredProcedureArticleScripts method. This methodeventually calls the Microsoft.SqlServer.Management.Smo.ParameterBase.CheckParamName method. The CheckParamName method raises the WrongPropertyValueException exception if full-width underscore characters (＿) are used in the name of the parameter.