SQL tips for database administrator RSS 2.0
# Saturday, 13 December 2008

If you are using SQL authentication for the database connections in your SSIS package you may get the following error message when you schedule your SSIS package.

   Code: 0xC0016016


   Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

You can do the followings to fix the problem:

1.     1.       Change the Package Security ProtectionLevel from EncryptSensitiveWithUserKey to DontSaveSensitive

2.       2.      Enable package configurations by right click on the package design surface and click "Package Configurations.."  to start Package Configuration Organizer.

3.       Create configuration file using the Package Configuration Organizer.

4.       4.     Modify the configuration file and store the password information of your connection string in the configuration file

5.           5.      Run your package using the configuration file.



Saturday, 13 December 2008 01:17:25 UTC  #    Comments [0] -
# Thursday, 11 December 2008

One of my colleagues came to me and showed me a strange problem on SQL server. When a user uses Windows Login to connect to SQL server the user get the 18456 error message. But if the user is added to SysAdmin role the user is able to login to the SQL server. My colleague has deleted the window login from the SQL server and added the window login back to the SQL server. It does not resolve the problem.

The SQL Server Logs shows the following message:

SQL Error Log Message:

Date  12/11/2008 9:30:16 AM

Log  SQL Server (Current - 12/11/2008 2:50:00 PM)

Source  Logon


Error: 18456, Severity: 14, State: 11.


The followings are the steps that we used to solve this problem:

1.       Check if the login has granted access permission to the default database

2.       Check if the login is not disable

3.       Check if the login is denied database engine access

4.       Run the following script:

Exec  xp_logininfo  'domain\user'

 (Replace the domain with your domain name and user with the right user name)

If there is no result returning back, then the window login is denied database engine access through windows group.

5.       Run the following script to get a list of window groups that are denied SQL database engine access

select prin.[name], prin.type_desc

from sys.server_principals prin

JOIN sys.server_permissions perm on prin.principal_id = perm.grantee_principal_id

where perm.state_desc = 'DENY'

6.       Make sure that the window login is not a member of the Window Groups in the list.

Thursday, 11 December 2008 23:39:15 UTC  #    Comments [1] -
SQL Security
<2008 December>
Total Posts: 6
This Year: 0
This Month: 0
This Week: 0
Comments: 2
All Content © 2017, Full Time DBA