SQL Server 2008 Cluster installation on Windows 2008 Enterprise Server

SQL Server 2008 Cluster installation on Windows 2008 Enterprise Server
By Jeff Han 2009.09.30

1. Windows 2008 cluster ring installation

When you are trying to create windows 2008 cluster ring, you will get error like this:
“An error occurred while creating the cluster. An error occurred creating cluster ‘vanpgsql08clus’. The network location cannot be reached.”

It looks like 2008 Cluster requires a system share for it to be created. However, by default windows 2008 server system share was disabled by a registry key. In order to enabling system share, you have to modify this key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\
“AutoShareServer”=dword:00000000
To:
“AutoShareServer”=dword:00000001
After that the cluster can be created successfully.

Cluster ring configurations:

Cluster Name: vanpgsql08clus IP address: 10.165.21.132
Node1: vanpgsqlc08n1 IP address: 10.165.21.133
Node2: vanpgsqlc08n2 IP address: 10.165.21.134
MSDTC: vanpgsql08cldtc IP address: 10.165.21.135

2. SQL Server 2008 cluster installation
The SQL Server 2008 cluster installation is quite different than earlier versions. It doesn’t do the installation for the remote nodes. (See more at Ref. 1)
There are two installation options:
* Integrated failover cluster install with Add Node
SQL Server integrated failover cluster installation consists of two steps:
1. Create and configure a single-node SQL Server failover cluster instance. At the completion of a successful configuration of the node, you have a fully functional failover cluster instance. At this time it does not have high-availability because there is only one node in the failover cluster.
2. On rest nodes (NOT ON FIRST ONE) to be added to the SQL Server failover cluster, run Setup with Add Node functionality to add that node.

* Advanced/Enterprise installation
SQL Server Advanced/Enterprise failover cluster installation consists of two steps:
1. On each node that will be part of the SQL Server failover cluster, run Setup with Prepare Failover Cluster functionality. This step prepares the nodes ready to be clustered, but there is no operational SQL Server instance at the end of this step.
2. After the nodes are prepared for clustering, run Setup on the node that owns the shared disk with the Complete Failover Cluster functionality. This step configures and completes the failover cluster instance. At the end of this step, you will have an operational SQL Server failover cluster instance.

In this case we used option 1 – “integrated failover cluster install with add node”

Step1: usually we start installation by run “setup” to bring the GUI interface. However, sometimes a fully functional cluster ring couldn’t pass the sql server 2008 validation rule which will block the installation. In our case it’s blocked, so we have to skip this validation rule to let installation carry on (see more at ref.6):
Here are some commands to bypass the rule check:
* For an integrated failover setup, run the following command on each node that is being added:
Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster
* For adding node to integrated installation instance
Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode
* For an advanced or enterprise installation, run the following command:
Setup /SkipRules=Cluster_VerifyForErrors /Action=CompleteFailoverCluster
So the first step here we run (c:\temp is my installable location):
C:\temp\Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster
We use the configuration as below:

Cluster mode: Active – Passive two nodes cluster
SQL Network name: vanpgsqlc2k8vip IP address: 10.165.21.136
Service Account: pgdev\service.pg.prod
Cluster domain group: pgdev\sqlcluster
SA password: 4Sqlserver

Step 2: According to the integrated failover cluster installation method, we have to run add node(by GUI/Command line) on second node to add this node into cluster. Because of the validation problem we run this here on node 2:
C:\temp\Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode
However, Microsoft gave us an error again:
“The current SKU is invalid”
This is actually a installation bug (see more in ref. 7), so we download the hotfix (SQLServer2008-KB973601-x86), patched first node and second node(maybe not necessary). The patch apparently will failed on first node, but it’s ok you can bring the services up manually in cluster admin GUI.
After patching the installation worked well.

Step3: testing failover between two nodes
No problem at all

Reference:
1. “Getting Started with SQL Server 2008 Failover Clustering”
http://msdn.microsoft.com/en-us/library/ms189134.aspx

2. “Setting up a Two-node SQL Server 2008 Cluster from the Command Prompt – Preparation”
http://www.databasejournal.com/features/mssql/article.php/3771296/Setting-up-a-Two-node-SQL-Server-2008-Cluster-from-the-Command-Prompt—Preparation.htm

3. “Setting up a Two-NODE SQL Server 2008 Cluster from the Command Prompt – Integrated Installation”
http://www.databasejournal.com/features/mssql/article.php/3772826/Setting-up-a-Two-NODE-SQL-Server-2008-Cluster-from-the-Command-Prompt—Integrated-Installation.htm

4. “Installing a Two-node SQL Server 2008 Cluster – Advanced option”
http://www.databasejournal.com/features/mssql/article.php/3783326/Installing-a-Two-node-SQL-Server-2008-Cluster—Advanced-option.htm

5. “Installing SQL Server 2008 on a Windows Server 2008 Cluster Part 1-4”
http://www.mssqltips.com/tip.asp?tip=1687

6. “Cluster validation rule of the setup process failure workaround”
http://support.microsoft.com/kb/953748

7. “Error message when you try to add a second node to a SQL Server 2008 failover cluster: “The current SKU is invalid””
http://support.microsoft.com/kb/957459/

enable a disabled sql agent service

When SQL Server 2005 Management Studio’s Object Browser shows the SQL Server Agent service with a red down arrow and the text Agent XP’s disabled, the service is not started or disabled.
i had this issue on my sql 2005 active-active cluster even with sql agent resource started in my cluster admin interface, the agent actually was not running.

To fix this, you need to open the sql server advance configuration and enable sql agent, then close the advance configuration option again:
–step 1. open advanced configuration option
exec sys.sp_configure @configname = N’show advanced options’, @configvalue = 1
reconfigure with override

–step 2. change value from “0″ to “1″ which means enable
exec sys.sp_configure @configname = N’Agent XPs’, @configvalue = 1
reconfigure with override

–step 3. close advanced configuration option
exec sys.sp_configure @configname = N’show advanced options’, @configvalue = 0
reconfigure with override

table usages for ms sql database

This STP help us find usage for any objects (includes table)
sp_spaceused [object_name]

By combining other things you got full table usage list from db
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?’”

–or

select ‘exec sp_spaceused ‘+name from sys.tables order by name;

MS SQL Server 2005 alias doesn’t work on 64bit platform

we recently upgraded our 1 cluster from 32bit platform to 64bit platform.
In order to launch the replication monitor we have to use sql alias name which has been set up on “sql server configuration Manager” -> “sql native client configuration ” -> “alias”.

for 32bit sql client, there is only one option to set up alias. so from 32bit client to connect to 64bit server by using alias, not a problem!

However, if you like me are using 64bit sql client, and happen to set up the alias under “sql server configuration Manager” -> “sql native client configuration ” – “alias”, you will fail to connect to the server by using this alias.
The right spot you setup the alias should be “sql server configuration Manager” -> “sql native client configuration(32bit) ” – “alias”.

i have no idea why on 64bit client you still need to use 32bit client configuration for alias connection. But that is bloody truth:)

Note: if you are using sql server 2005 cluster then you have to keep the same alias in both of 32bit and 64bit client configuration. Otherwise, by missing alias in the 64bit one, you will get trouble with all the jobs and logs

two ways to call Oracle Stored procedure from VB.net

PROCEDURE TEST_C(temp out varchar2,a IN varchar2, b in varchar2)
IS
BEGIN
temp:=a || b;
END;

Solution 1:
add “Imports System.Data.OleDb” at the code beginning

Dim dbConn As New OleDbConnection
Dim dbComm As OleDbCommand

dbConn.ConnectionString = “Provider=MSDAORA;User ID=xxx;Password=xxx;Data Source=xxx;”
dbConn.Open()
dbComm = dbConn.CreateCommand

dbComm.Parameters.Add(“temp”, OleDbType.VarChar, 30).Direction = ParameterDirection.Output
dbComm.Parameters.Add(“a”, OleDbType.VarChar, 30).Direction = ParameterDirection.Input
dbComm.Parameters(“a”).Value = “test “
dbComm.Parameters.Add(“b”, OleDbType.VarChar, 30).Direction = ParameterDirection.Input
dbComm.Parameters(“b”).Value = “OK”

dbComm.CommandText = “TEST_C”
dbComm.CommandType = CommandType.StoredProcedure
dbComm.ExecuteNonQuery()
dbConn.Close()

MessageBox.Show(dbComm.Parameters(“temp”).Value)

Solution 2:
add “Imports System.Data.OracleClient” at the code beginning

Dim oraConn As New OracleConnection
Dim oraComm As New OracleCommand

oraConn.ConnectionString = “Data Source=xxx;User Id=xxx;Password=xxx”
oraComm.Connection = oraConn

oraComm.Parameters.Add(“temp”, OracleType.VarChar, 10).Direction = ParameterDirection.Output
oraComm.Parameters.Add(“a”, OracleType.VarChar, 10).Direction = ParameterDirection.Input
oraComm.Parameters(“a”).Value = “test “
oraComm.Parameters.Add(“b”, OracleType.VarChar, 10).Direction = ParameterDirection.Input
oraComm.Parameters(“b”).Value = “OK”

oraConn.Open()
oraComm.CommandText = “TEST_C”
oraComm.CommandType = CommandType.StoredProcedure
oraComm.ExecuteNonQuery()
oraConn.Close()

MessageBox.Show(oraComm.Parameters(“temp”).Value)

Note: the first parameter name has to be the same as the oracle stored procedure parameter name;
if there is dblink in the stored procedure, then the solution 1 is the only choice.

if you are trying to call oracle stored procedure by passing clob/lob as parameters, then don’t use odbc solution because it has 32k limitation. Oracle have this metalink note talk about this:
From metalink: Subject-32k Limitation When Passing LOB Parameter Through Stored Procedure Doc ID: 252102.1
https://metalink2.oracle.com/metalink/plsql/f?p=130:14:6916025231277951933::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,126125.1,1,0,1,helvetica

Workaround
~~~~~~~~~~

Workaround 1:

Use the Oracle Provider for OLEDB instead, making sure to set the command
property SPPrmsLOB to TRUE:

objCmd.Properties(“SPPrmsLOB”) = TRUE

Workaround 2:

Instead of passing a CLOB as a parameter to a stored procedure, use a method
that directly interfaces with the database and does not require the use of
a stored procedure to update the CLOBs as in the following example:

Note 126125.1 – ADO Streaming BLOB & CLOB Example Using ODBC and OLEDB in VB (SCR 1388).

11g rac could not be started

Today after reboot the rac nodes servers, the RAC 11g couldn’t be started.
Here is the errors and solutions:

Errors:
1.[root@vanpgrepdb03 racg]# crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

[root@vanpgrepdb03 racg]# crsctl check crs
Failure 1 contacting Cluster Synchronization Services daemon
Cannot communicate with Cluster Ready Services
Cannot communicate with Event Manager

[root@vanpgrepdb03 racg]# ps -ef|grep -i init.d
root 3895 1 0 Jun21 ? 00:00:00 /bin/sh /etc/init.d/init.evmd run
root 3896 1 0 Jun21 ? 00:00:00 /bin/sh /etc/init.d/init.cssd fatal
root 3897 1 0 Jun21 ? 00:00:00 /bin/sh /etc/init.d/init.crsd run
root 3961 3895 0 Jun21 ? 00:00:04 /bin/sh /etc/init.d/init.cssd startcheck
root 4031 3896 0 Jun21 ? 00:00:04 /bin/sh /etc/init.d/init.cssd startcheck
root 4123 3897 0 Jun21 ? 00:00:04 /bin/sh /etc/init.d/init.cssd startcheck
root 5230 24639 0 12:58 pts/0 00:00:00 grep -i init.d

–check the system log
[root@vanpgrepdb03 racg]# tail -f /var/log/messages
Jun 26 13:15:49 vanpgrepdb03 automount[3295]: create_udp_client: hostname lookup failed: Operation not permitted
Jun 26 13:15:49 vanpgrepdb03 automount[3295]: create_tcp_client: hostname lookup failed: Operation not permitted
Jun 26 13:15:49 vanpgrepdb03 automount[3295]: lookup_mount: exports lookup failed for d
Jun 26 13:15:49 vanpgrepdb03 logger: Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.4031.
Jun 26 13:15:49 vanpgrepdb03 logger: Cluster Ready Services waiting on dependencies. Diagnostics in /tmp/crsctl.4031.

–check the trace file
[root@vanpgrepdb03 racg]# cat /tmp/crsctl.4031
Oracle Cluster Registry initialization failed accessing Oracle Cluster Registry device: PROC-26: Error while accessing the physical storage Operating System error [Permission denied] [13]

–verify the raw file to see if they are binded
[root@vanpgrepdb03 ~]# raw -qa
/dev/raw/raw1: bound to major 8, minor 1
/dev/raw/raw2: bound to major 8, minor 2

–check the permission since the log was mentioning that
[root@vanpgrepdb03 ~]# cd /dev/raw
[root@vanpgrepdb03 raw]# ls -al
total 0
drwxr-xr-x 2 root root 80 Jun 21 07:08 .
drwxr-xr-x 14 root root 3760 Jun 24 08:17 ..
crw——- 1 root root 162, 1 Jun 21 07:08 raw1
crw——- 1 root root 162, 2 Jun 21 07:08 raw2
–looks like the permission is not correct

–change permissions (on both nodes)
[root@vanpgrepdb03 raw]# chown oracle:dba /dev/raw/raw1
[root@vanpgrepdb03 raw]# chown oracle:dba /dev/raw/raw2
[root@vanpgrepdb03 raw]# chmod 660 /dev/raw/raw1
[root@vanpgrepdb03 raw]# chmod 660 /dev/raw/raw2
[root@vanpgrepdb03 raw]# chown oracle:dba /dev/sda1
[root@vanpgrepdb03 raw]# chown oracle:dba /dev/sda2
[root@vanpgrepdb03 raw]# chmod 660 /dev/sda1
[root@vanpgrepdb03 raw]# chmod 660 /dev/sda2

–after that, chheck the init.cssd, it’s up!
[root@vanpgrepdb03 raw]# ps -ef|grep init.d
root 3895 1 0 Jun21 ? 00:00:00 /bin/sh /etc/init.d/init.evmd run
root 3896 1 0 Jun21 ? 00:00:03 /bin/sh /etc/init.d/init.cssd fatal
root 3897 1 0 Jun21 ? 00:00:00 /bin/sh /etc/init.d/init.crsd run
root 7588 3896 0 13:25 ? 00:00:00 /bin/sh /etc/init.d/init.cssd oprocd
root 7606 3896 0 13:25 ? 00:00:00 /bin/sh /etc/init.d/init.cssd oclsomon
root 7630 3896 0 13:25 ? 00:00:00 /bin/sh /etc/init.d/init.cssd daemon
root 20251 6701 0 14:15 pts/0 00:00:00 grep init.d

–check the crs service is also working now
[root@vanpgrepdb03 db]# crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy

–bring up the rac resources again by using srvctl

Reference:
“why my oracle cluster could not start” http://surachartopun.com/2009/04/why-my-oracle-cluster-could-not-start.html

Oracle connection idle timeout with firewall

Since our oracle 10g RAC has been moved behind firewall, we always get disconnected/timeout by firewall if the connection was idle.
By searching the metalink i found this article is really useful:

Resolving Problems with Connection Idle Timeout With Firewall

An Overview

Firewall(FW) has become common in today’s networking to protect the network environment. The firewall recognizes the TCP protocol and it records the client server socket end-points. Also, FW recognize the TCP connection closure, and then will release the resources allocated for recording the opening connection. For every end-point pairs , the firewall must also allocate some resources(may be small).

When the client or server closes the communication it sends TCP FIN type packet, this is a normal socket closure. However, it is not uncommon that the client server communication abruptly ending without closing the end points properly by sending FIN packet, for example, when the client or server crashed, power down or a network error which prevents sending the closure packet to the other end. In that cases, the firewall will not know that the end-points will no longer use the opened channel. As a passive intermediary, it had no way to determine if the endpoints are still active. As is it not possible to maintain resources forever, and also, it is a security threat keeping a port open for undefined time. So, firewall imposes a BLACKOUT on those connections that stay idle for a predefined amount of time.

Initially FW were designed to protect the application servers, network and then to protect client/server connection. With these in mind, a time-out in terms of hours (1 hour is the default for most FW) is reasonable. With the advent of more complex security schemes, FW are not only between client and server, but also between different application servers ( intranet, demilitarized zone (DMZ) , and such) and database servers. So, the horizon of 1 hour idle time for communication between servers maybe not be appropriate.

Idle connections can be expected from an application server. There is the case of J2EE using pooled JDBC connections. The pool usually returns the first available connection to the requester, so the first connections of the pool list are the most likely to be active. The last one, which are at the end of the list, are only used at peek loads, and most of the time it will be inactive.

Other cases are the connections established from a HTTP Server, either SQL connections from mod_plsql, or AJP connections from mod_oc4j.

Blackout

One of the inconvenience of theses blackout, is that they are passive. None of the endpoints will be notified that the communication was banned . Only when the client or server tries to contact its peer, it comes to know that the peer end is no more active and the communication has already been broken.

The worst of all scenarios are the so called “passive listeners” . They will never know. Because, passive listeners are those processes at an endpoint that are simply waiting for commands to arrive from the other end. A typical example of this are the backend database server processes, which are reading from the socket looking new SQL statements to execute , and after the request is answered, they return to their passive state. When a blackout occurs, they will stay forever in this reading state, unless some of the following techniques are applied.

Resolving problems with connection idle time-out

TCP KeepAlive

You can enable TCP KeepAlive option at the Operating System(OS) level. Once TCP keepalive option is enabled and configured, a small probe packet will be sent to the other end at every predefined in-activity interval and it expects an ACK from the other end. And ACK will be returned only when the other end is alive and is reachable. If ACK is not returned, then after some retry, the OS will close the end points and will release the resources allocated for that. The application which is listening on that particular socket will recieve the error, so that application can take necessary action upon receiving the error signal from the OS.

When a communication is blacked out by the firewall, the probe will not reach its other end, and then the OS will close the socket end points and the application will be notified of the exception.

Steps to configure TCP KeepAlive depends on a specific Operating Systems. You will have to refer the appropriate OS documentation for it.

It is common to enable TCP KeepAlive option at the server end. Because server is the one which holds many resources for a communication, it any communication is broken, then those resources at the server will be released than holding it for indefinite time. By default TCP KeepAlive is not enabled at the OS.

TCP KeepAlive is applicable for all network applications running on that particular Operating System.

DCD for DataBase Servers

For database connections, one of the endpoints is a passive listener, either is a dedicated process or a dispatcher process. If the connection becomes blacked out , this backend will never know that client cannot send any more requests, and then will lock important resources as database sessions, locks , and at least , a file descriptor used for maintaining the socket.

A solution is to make this backend “not so” passive, using the DCD (dead connection detection) to figure out if the communication is still possible.

Simply, set in the $ORACLE_HOME/network/admin/sqlnet.ora, in the server side SQLNET.EXPIRE_TIME=10 (10 minutes, for example). With this parameter in place, after 10 minutes of inactivity, the server send a small 10 bytes probe packet to the client. If this packet is not acknowledge, the connection will be closed and the associated resources will be released.

There are two benefits with this DCD
1. If the SQLNET.EXPIRE_TIME is less than the FW connection idle time-out, then the firewall will consider this packet as activity, and the idle time-out (firewall blackout) will never happen until both the client and the server processes are alive.

2. If the SQLNET.EXPIRE_TIME (let’s say a little bit higher) than the FW idle limit, then , as soon as the blackout happens , the RDBMS will know and will close the connection.

The first case is recommended when the connection comes from another application server , and the second makes sense for client applications.

DCD works at the application level and also works on top of TCP/IP protocol. If you have set the SQLNET.EXPIRE_TIME=10 then do not expect that the connections will be closed exactly after 10 minutes of the blackout or network outage. The TCP timeout and TCP retransmission values also adds to this time.
Please note that some latest firewalls may not see DCD packets as a valid traffic, and thus the DCD may not be useful. In this case, firewall timeout should be increased or users should not leave the application idle for longer than the idle time out configured on the firewall.

AJP Connections

It is not a default behavior in 9.0.2, but if Patch 2862660 is installed, the connection between and OHS server process and the J2EE can be maintained for more than a single request. If the parameter Oc4jConnTimeout is set, the OHS will maintain the connection for at least that time. The problem is that the child process may became inactive before that time-out occurs, and then the connection will remain open. While the child process is inactive, the connection will be idle, and there is chance to be blackout by the FW.

If this happens, the first thing that the child will do is to close it when it becomes active. But at this time, the TCP socket closing cannot be completed, due the blackout. Although the http child process can simply ignore the closing failure and continue the creation of a new connection, the passive listener at the
j2ee side (the worker thread) will be hook without a chance for the resources to be released.

To solve this , the Patch 3151686 must be installed and the java-option
-Dajp.keepalive=true
must be enabled.

After this, the blackout detection will rely on the TCP KeepAlive provided by the operating system.

As DCD , this process consist in send probes -empty packages- when a socket had been inactive for a period of time. If there is no response, the socket will be closed, and then, even the passive listener, will receive and exception or signal to let him know that the no further communication is possible.

CONCLUSION

As the firewalls extend their functionality , and are now are placed in between application servers, some tuning and parameter adjusting must be made to overcome the default rules established for client/server communications.

Note id: 257650.1
Link: https://metalink2.oracle.com/metalink/plsql/f?p=130:14:9497610239050450074::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,257650.1,1,1,1,helvetica

export name case-sensitive tables in oracle

exp xtreme file=xtreme_tables.dmp tables=(‘\”Product\”‘,’\”Supplier\”‘,CUSTOMER,ORDERS)

How to make user to view they jobs in sql 2005

i tried to make user able to see their own jobs in sql 2005. First thing you need to assign job owner as user’s account.

Second thing, sql 2005 server has some new server roles (on MSDB database user only!) for managing sql agent stuff. Here is the screenshot:

With SQL Server 2005, a user must be a member of one of the new fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent. When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. After this basic introduction let us identify what are the specific uses of each role.

SQLAgentUserRole

This is the least privileged role among new three new fixed server roles. This role has permissions only on operators, local jobs, and job schedules. These types of users can only view the operator but they can’t create, modify or delete operators. They cannot change job ownership to gain access to jobs that they do not already own. SQLAgentUserRole members can view a list of available proxies only in the Job Step Properties dialog box of SQL Server Management Studio. These users cannot delete job history.

SQLAgentReaderRole

SQLAgentReaderRole has all the options that SQLAgentUserRole has. In addition, it does have permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own.

SQLAgentOperatorRole

SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. SQLAgentOperatorRole members have additional permissions on local jobs and schedules. They can execute, stop, or start all local jobs, and they can delete the job history for any local job on the server.

Link: http://www.sqlservercentral.com/articles/Administration/sqlserveragent2005/2376/

How to connection sql server named instance SSIS service

I’ve occasionally run into the following error when attempting to connect to SSIS from Management Studio:

"Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Named Pipes Provider: Could not open a connection to SQL Server [2]. (MsDtsSvr)."

The problem, as it turns out, is that when I installed SQL Server 2005/2008 on a machine, that a default instance was already installed. In my case SQL Server 2000 was already installed as the default instance. The initial posts I read (and heard about in talking to other colleagues who had the same problem) said that SQL Server 2005/2008 had to be installed as the default instance  if you wanted to be able to use SSIS.

The problem is that unlike the database engine, which can have many running named instances running, SSIS can only run one instance on a server at a time. When SQL Server 2005/2008 is installed there is no option to specify which SQL Server instance should be used to run SSIS. Instead the default configuration is always applied and SSIS tries to start under the default instance. If the default instance is SQL Server 2000, you have a problem. You can only run SSIS on SQL Server 2005 or higher.

Since a server can only run one instance of SSIS you need to make sure that the config file points to the correct instance of SQL Server. Locate the MsDtsSrvr.ini.xml configuration fileand open it with a text or xml editor.

SQL 2005 location:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2008 location:
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

The default contents of the file:

<?xml version=”1.0″ encoding=”utf-8″?>
<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<StopExecutingPackagesOnShutdown>true </StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type=”SqlServerFolder”>
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type=”FileSystemFolder”>
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

Notice the ServerName element points to the default instance of SQL Server (when connecting to the local machine “.” is the same as “(local)” – they both use NamedPipes to connect).

Modify the configuration file to point to the named instance as follows.

<?xml version=”1.0″ encoding=”utf-8″?>
<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
<StopExecutingPackagesOnShutdown>true </StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type=”SqlServerFolder”>
<Name>MSDB</Name>
<ServerName>.\SQL2005</ServerName>
</Folder>
<Folder xsi:type=”FileSystemFolder”>
<Name>File System</Name>
<StorePath>..\Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

After making the change you MUST restart the SSIS service for the change to take effect.

Another normal issue to connect to SSIS in sqlserver 2005 is “access is denied”. The following workground can resolve this issue(http://msdn.microsoft.com/en-us/library/aa337083.aspx):

1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.
3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.
5. Right-click on MsDtsServer and select Properties.
6. In the MsDtsServer Properties dialog box, select the Security tab.
7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.
8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.
9. Click OK to close the dialog box.
10. Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.
11. Close the MMC snap-in.
12. Restart the Integration Services service.