Security of a package - SSIS

When you are designing a SSIS package, at times there will be several other developers need to work on the same package, but you need a setting that when you open the package you do not have to reenter the username and package, but when another user opens the package the system should ask for the authentication details.

How can you achieve this.

In the package click on properties --- protection level

The following options are available for selection

• Do Not Save Sensitive will remove sensitive data every time the package is reopened.

• The Encrypt Sensitive With User Key protection level encrypts the sensitive information in the package by using keys based on the current user. When a new user opens the package, he or she must reenter sensitive data, such as a SQL Server password.

• Encrypt Sensitive With Password will encrypt sensitive data with a package password. This would require you to enter a password every time you opened the package.

• Encrypt All With User Key will encrypt the entire package with the user key. A new user will not be able to view or execute the package.

Data Transfer slow - SSIS package

In a case where we are transferring data from a source to destination is slow using the data flow task in SSIS package, the following can be considered.

In the destination task --- oledb destination task , the OLE DB Destination, when connected to SQL Server, enables data to be inserted with bulk insert statements by using Fast Load, click on the olebd destination properties --- click on access mode --- select fast load

Error: 18456, Severity: 14, State: 16

Error: 18456, Severity: 14, State: 16


This error is caused based on the user password is not correct from the client system. This message appears in the log file in this location C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

This error also appears on the event viewer.

Error: 18456, Severity: 14, State: 16
Login failed for user 'sa'. [CLIENT: 192.168.x.xxx]

If this message appears continously check with client system if there is any sql job running and trying to attempt to connect to the server and password is not set right.

In our case we had a diagnostic tool running from this client and the password was changed, this has caused this error message

SQL SSRS Report optimization

Filtering the dataset might be slower than using the parameters when you are not using a cached report.

Filtering the dataset enables you to use a cached report that reuses the same data.

Filtering the dataset means that SQL Server Reporting Services will query all the data and later filter the information. The advantage of filtering the dataset is that other reports can use the same data when using cache reports, increasing their performance. However, if the report is not configured as cached, filtering the dataset will likely be slower than using parameters.

Sql Connection

Error - 26 A network related or instance-specific error occured while establishing a connection to SQL Server. The server was not found or not accessible. Verify the instance name is correct and the sql server is configured to allow remote connections

For the above please check the following.

Option 1
1. Log into sql server , go to start --- control panel ---administrative tools --- services
2. Check sql server and make sure it is running.
3. Check for sql browsing services and make sure it is running.

Option 2
1. go to start --- programs --- administrator tools --- computer management
2. Click on services and applications
3. Click on sql server 2005 network configurations
4. Click on protocols for MSSQL Server
5. Click on TCP/IP
5. click on IPAddresses tab , check for the ports to be 1433

Option 3


1. check the main  firewall , please make sure you contact your network admin when you do this.
2. Under the change settings
3. Go to the exception tab
4. add port
5. Name : SQLPort Number: 1433Protocol: Select TCP

We had the same issue again on 20th July 2012 where Brian had a condition to allow the ports, this was removed by Brian in the main firewall and it worked.

SQL Server Profiler results

To check the sql server profiler results in case of monitoring SSAA the following are the ways to measure.

1. The integer data displays the numerical information associated with the events.

2. The text data column shows the text description of the event such as MDX statement

3. The EventSubclass column shows the event subclass, such as the ExecuteSQL, WriteData, BuildIndex, or other subclass.

4. The duration displays the event duration.

SQL Server Names Instance installation

SQL Server Named Instance --- Connection issue

I have created a second instance in my sql server as MSSQL2, after the installation I tried to connect using the SSMS sql server management studio, but to my surprise i was given a error to check if the sql server exists or user details are correct.

The first instance connected without issues. But the second instance MSSQL2 will not connect.
I did check my authentication details, username and password.
I did check the sql surface area configureation details to see the MSSQL2 is online.
I did check the services to see if the MSQL2 is online.

But the issue was the sql server browser service was NOT online. When I brought is online i was able to connect without issues.