If you have ever attempted to connect to a named instance of SSIS with management studio you may have had the frustration of getting an error message similar to “Failed to retrieve the data for this request”. The issues is that during the installation of SSIS the file MsDtsSrvr.ini.xml is created in the “drive:\Program Files\Microsoft SQL Server\90\DTS\Binn” that tells the SSIS service what SQL Server instance to connect to for enumerating packages stored in the MSDB database. By default, it is set as '.' which means the local server name, default instance. To connect to a named instance you need to modify the “ServerName” property to specify the named instance:
<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\InstanceName</servername>
</folder>
<folder xsi:type="FileSystemFolder">
<name>File System</name>
<storepath>..\Packages</storepath>
</folder>
</toplevelfolders>
</dtsserviceconfiguration>
Once the change has been made you need to restart the service in order for the change to take effect. Once this has been done you will be able to connect using SSMS, but when connecting locally rather than specifying the servername\instancename only the servername should be provided or you will receive an error stating that named instances in SSIS are not supported. This seems a bit strange, but as the MsDtsSrvr.ini.xml has the specific instance the service is able to connect. When connecting remotely then you will use the servername\instancename
MSDN has documented changing the MsDtsSrvr.ini.xml file at:
http://msdn.microsoft.com/en-us/library/ms137789.aspx
Wednesday, November 18, 2009
Tuesday, November 17, 2009
SQL Server 2008 Auditing
There have been instances where I have had the need to record the last time a table was viewed, SELECT(ed) or even been requested to provide a record of who was accessing specific records. Previous to sql server 2008 the ability to perform such a task was limited to creating a custom trace in profiler which could result in un-needed overhead in a production environment. SQL Server 2008 provides the ability to create such a trace natively using the new sql server audit.
Creating the Objects
In this example I will create a database audit specification that will monitor all select statements made against the Person.Person table in the AdventureWorks2008 database from the role of dbo. The first step is to create a server-level audit that will define the audit name and where and how the audit will be stored. These objects can be created using the object explorer in SSMS, but I will demonstrate how to create them using t-sql in this illustration. You have the ability to store the audit log either in the application log, security log, or a file path. In this example I will use a file path to store the audit log stored in the Audit folder on the C drive using the below listed code:
All server-level audits must be created in the master database and after creating the audit you must set the state to on using an alter statement as it cannot be included in the create statement. You will probably notice that only a path is declared and not a file name as the file name will be generated automatically based on the audit name and guid. The server level audit can also be viewed in the Object Explorer under the server Security folder in the Audits folder.
Once this is complete you can create your database audit specification that will declare the server level audit you just created. The below statement creates a database audit specification named Person_Select that will log all select queries against the Person.Person table made from the principal dbo:
Again the audit specification is contained under the database Security folder in the Database Audit Specifications folder. Once the audit specification is created assure that you are logged in as a user in the dbo role and execute the statements below.
The results appear as this:
In my from clause I included the file path followed by a *, which specifies to read all audit logs within the specified path. The full documentation for the new auditing capability and relative functions can be found in sql server books on line along with several examples.
Conclusions
This provided a simple example of how to implement sql server 2008’s new auditing features. There are a numerous features and configurations that I did not touch on, but full documentation can be found in sql server 2008 books on line and on msdn at the below link:
Creating the Objects
In this example I will create a database audit specification that will monitor all select statements made against the Person.Person table in the AdventureWorks2008 database from the role of dbo. The first step is to create a server-level audit that will define the audit name and where and how the audit will be stored. These objects can be created using the object explorer in SSMS, but I will demonstrate how to create them using t-sql in this illustration. You have the ability to store the audit log either in the application log, security log, or a file path. In this example I will use a file path to store the audit log stored in the Audit folder on the C drive using the below listed code:
--Server level audit must be created in the master database
USE master
CREATE SERVER AUDIT Select_Audit
TO FILE(FILEPATH='C:\AUDIT\')GOALTER SERVER AUDIT Select_AuditWITH (STATE=ON)GOAll server-level audits must be created in the master database and after creating the audit you must set the state to on using an alter statement as it cannot be included in the create statement. You will probably notice that only a path is declared and not a file name as the file name will be generated automatically based on the audit name and guid. The server level audit can also be viewed in the Object Explorer under the server Security folder in the Audits folder.
Once this is complete you can create your database audit specification that will declare the server level audit you just created. The below statement creates a database audit specification named Person_Select that will log all select queries against the Person.Person table made from the principal dbo:
USE AdventureWorks2008
CREATE DATABASE AUDIT SPECIFICATION Person_Select FOR SERVER AUDIT Select_Audit
ADD(SELECT ON Person.Person BY dbo)
WITH (STATE = ON)GOAgain the audit specification is contained under the database Security folder in the Database Audit Specifications folder. Once the audit specification is created assure that you are logged in as a user in the dbo role and execute the statements below.
SELECT *
FROM Person.Person;
GOSELECT *FROM Person.PersonWHERE LastName = 'Duffy';
GO
You are able to view the audit file using the system stored procedure sys.fn_get_audit_file
SELECT event_time,
action_id,
session_server_principal_name,
statementFROM sys.fn.get_audit_file('C:\Audit\*', DEFAULT, DEFAULT)
The results appear as this:
In my from clause I included the file path followed by a *, which specifies to read all audit logs within the specified path. The full documentation for the new auditing capability and relative functions can be found in sql server books on line along with several examples.
Conclusions
This provided a simple example of how to implement sql server 2008’s new auditing features. There are a numerous features and configurations that I did not touch on, but full documentation can be found in sql server 2008 books on line and on msdn at the below link:
Subscribe to:
Posts (Atom)
