In the previous
article of our series covering SQL Server 2005 Integration Services, we
discussed Control Flow tasks designed to simplify the transfer of SQL Server
objects (such as databases, user-defined error messages, and SQL Server Agent
jobs). We will complete this overview by describing the characteristics of the
remaining tasks in this category – two of which are intended specifically for
server logins and master stored procedures – and one (called Transfer SQL
Server Objects task) with more universal purpose.
However, before we focus on these topics, we need to first take a closer
look at SQL Server 2005 and Windows specific characteristics that have
potential impact on successful operation of SSIS transfer tasks. This includes the
authentication procedure used when creating connections to remote computers,
configuration defaults that affect communication via these connections, as well
as security settings introduced in the latest service packs for Windows XP and
2003 Server operating systems.
Let’s start with the authentication mechanism (for its more detailed
overview, refer to one of
our earlier articles). SQL Server 2005 supports two modes that can be
applied to validate identity during login. The first one, called Windows
Authentication, uses credentials of the Windows account that belongs to a user
initiating the logon to establish whether access to the server should be
permitted (following this step, the authorization process determines the level
of privileges that are granted to this login). In addition to its inherent
security, this process is transparent from the user’s perspective, since it
does not prompt for username or password. The second mode (which you can switch
to from the Security section of the Server Properties dialog box in SQL Server Management
Studio), known as SQL Server and Windows Authentication, still supports the
first method, but also allows using accounts defined within SQL Server itself.
In situations when connection via such an account is attempted, its credentials
need to be explicitly provided. While this is essentially identical to what was
available in earlier versions of SQL Server, there is a caveat that makes the
new implementation different. If you choose Windows Authentication mode at
installation time, but later you change it to include SQL Server
Authentication, the sa account will remain disabled. (To enable it, execute the
ALTER LOGIN command or use the Status section of the Login Properties dialog
box, which is invoked from the context sensitive menu of the sa entry under the
Security/Logins node of the SQL Server Management Studio). Keep in mind
however, that, in general, you should avoid authenticating with the sa account
(and rename it as a security precaution).
Another setting that might have impact on the outcome of SSIS transfer tasks
is configurable from SQL Server Configuration Manager. Within its Network
Configuration node, you can designate protocols (as well as specify their
parameters) for communicating with SQL Server. By default, only Shared Memory
is enabled – which is the simplest one to use (since it has no modifiable
options) but suitable only for local connections. Depending on your
requirements and preferences, you might need to allow Named Pipes or
TCP/IP-based connections (and configure them accordingly).
Among other factors, which can potentially affect server-to-server
communication are default Internet Connection and Windows Firewall settings
(component name and functionality depends on the operating system platform and
service pack version). Connectivity problems are likely to surface when running
SQL Server 2005 on a newly installed Windows 2003 Server (slipstreamed setup
with integrated SP1 files) or Windows XP SP2 computers, which by default block
all incoming traffic types. To address this issue, allow traffic on the TCP
port designated within the TCP/IP protocol section of SQL Server 2005 Network
Configuration (most commonly TCP port 1433), by identifying it as part of the firewall
exceptions.
Keeping all of these dependencies in mind, we are ready to return to our
original agenda. The first one on the list is Transfer Logins Task. Its purpose
is to allow members of the sysadmin fixed server role to duplicate any
arbitrarily chosen logins (with the exception of sa login, which can not be
copied) between two instances of SQL Server. In order to get familiar with its
characteristics, launch SQL Server 2005 Business Intelligence Development
Studio and create a new Integration Services project. Drag the Transfer Logins
Task from the Toolbox onto the empty Control Flow area and display the Editor dialog
box by selecting the Edit entry from its context sensitive menu. The General
section lets you alter the default name and description of the component. All
of the non-read only properties (including name and description) can also be
set dynamically using Property Expressions Builder, accessible from the
Expressions section. The Logins section contains remaining settings. That is where
you designate the Source and Destination connections that identify SQL Server
instances from and to which transfer will take place, as well as the required
authentication method (with associated credentials, in case SQL Server
Authentication is used). Once connections are available, you can assign one of
three available values (AllLogins, SelectedLogins, or AllLoginsFromSelectedDatabase)
to the LoginsToTransfer option. Depending on the value selected, you might need
to populate the LoginsList parameter (by selecting checkboxes next to the login
name in the Select Logins dialog box) or specify databases in the DatabasesList.
By default, in cases when a target server already contains a transferred login,
the task will fail. To change this behavior, assign IfObjectExists to Overwrite
or Skip, depending on the desired outcome. Finally, when transferring logins
based on Windows Authentication, it is possible to copy their associated
Security Identifiers (SIDs) by setting CopySids property to True.
While the task is intended to produce the same outcome regardless of which
authentication method is used (Windows or SQL Server), it turns out that at
this point, connections based on logins defined in SQL Server cause failures
during transfers. While they work flawlessly when the connection properties are
modified to use Windows authentication, such configuration requires that both
source and destination servers are members of the same or trusted Active
Directory domains (if this is not the case, you might have to resort to the
workaround outlined in the
Microsoft Knowledge Base article 246133). Note that the task allows you to
transfer logins based on Windows and SQL Server authentication (irrespective of
the type of authentication method chosen for source and destination
connections), however, SQL Server logins are automatically disabled once
transferred (and have to be manually enabled in order to become functional).
Transfer Master Stored Procedures task copies user-defined (but not system)
stored procedures stored in the master database from one instance of SQL Server
to another. To execute it, you will need dbo privileges to the master database.
Just as other tasks, which offer transfer functionality, its Editor interface contains
General and Expression sections (which serve the same roles as described in
earlier paragraphs). Its unique set of features is configurable from the Stored
Procedures section. From there, besides defining source and destination
connections, you can specify whether you want to transfer all stored procedures
(by setting the value of TransferAllStoredProcedures property to True) or only
selected ones (stored as values of the StoredProceduresList property). You can
also indicate how you want to handle duplicates (with IfObjectExists property,
which can be set to FailTask, Overwrite, or Skip). Interestingly, this task
completes successfully with both Windows and SQL Server authentication applied
for source and destination connections.
Transfer SQL Server Objects task was designed to be the most universal and
versatile of all the ones we have described so far. Its intended purpose is to
transfer any type of objects (their availability is dependent on SQL Server versions
involved in this operation) between two existing databases. This includes
tables (with associated items, such as indexes, triggers, primary and foreign
keys), views, stored procedures, user-defined functions, defaults, user-defined
data types for both SQL Server 2000 and 2005. Among SQL Server 2005-specific
objects are user-defined types based on the Common Language Runtime, partition
functions, partition schemes, assemblies, schemas, user-defined aggregates, and
XML Schema collections. As with other transfer tasks, you are given flexibility
of working with all objects of a particular type or select them individually
(there is also a CopyAllObjects option). The ability to perform a copy of SQL
Server logins, as well as database users and roles (by setting to True CopySqlServerLogins,
CopyDatabaseUsers, and CopyDatabaseRoles properties) provides the necessary
basis for copying object level permissions (controlled with CopyObjectLevelPermissions
property). This functionality is further enhanced by allowing dropping
corresponding objects at the destination, optional copy of data and schema,
applying collation from the source server, and including dependent objects
(applicable when transferring SQL Server 2005 user-defined types, which rely on
CLR assemblies).
Unfortunately, despite the feature-rich promising design, the implementation
of the Transfer SQL Server object task appears to be suffering from a few
problems. In particular, there seems to be an issue with transferring objects
that are associated with schemas other than dbo. In addition, successful
completion of the task might require in-depth analysis of object dependencies
(such as foreign and primary key relationships), which might not be properly
handled by auto-generated T-SQL scripts. Furthermore, it is necessary to verify
whether objects to be transferred already exist at the destination. The task
will fail in such case, unless you set the DropObjectsFirst property to True.
On the other hand, the failure will also occur if this property is enabled and
the destination database does not contain all of the objects you are trying to
transfer. The task suffers as well from the earlier described limitation, which
prevents the use of SQL Server accounts for remote server authentication. We
will look into alternative solutions to these issues in our next article.
