Setting the Protection Level of SSIS Packages

Setting the Protection Level of Packages To protect the data in an Integration Services package, you can set a protection level that helps protect just sensitive data or all the data in the package. Furthermore, you can encrypt this data with a password or a user key, or rely on the database to encrypt the data. Also, the protection level that you use for a package is not necessarily static, but changes throughout the life cycle of the package. You often set one protection level during development and another as soon as you deploy the package.

Do not save sensitive (DontSaveSensitive) - Suppresses the values of sensitive properties in the package when the package is saved. This protection level does not encrypt, but instead it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information.

Encrypt all with password (EncryptAllWithPassword) - Uses a password to encrypt the whole package. The package is encrypted by using a password that the user supplies when the package is created or exported. To open the package in SSIS Designer or run the package by using the dtexec command prompt utility, the user must provide the package password. Without the password the user cannot access or run the package.

Encrypt all with user key (EncryptAllWithUserKey) - Uses a key that is based on the current user profile to encrypt the whole package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility.

Encrypt sensitive with password (EncryptSensitiveWithPassword) - Uses a password to encrypt only the values of sensitive properties in the package. DPAPI is used for this encryption. Sensitive data is saved as a part of the package, but that data is encrypted by using a password that the current user supplies when the package is created or exported. To open the package in SSIS Designer, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. If the user tries to execute the package without providing the password, package execution fails.
Encrypt sensitive with user key (EncryptSensitiveWithUserKey) - Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. DPAPI is used for this encryption.

Rely on server storage for encryption (ServerStorage) - Protects the whole package using SQL Server database roles. This option is supported only when a package is saved to the SQL Server msdb database. It is not supported when a package is saved to the file system from Business Intelligence Development Studio.

SQL Command to know Parent-Child tables relationship

SELECT ParentTable.Name AS PTable, PK.Name AS PrimaryKeyForParent, FK.name AS ForeignKeyOfChild, ChildTable.name AS CTable

FROM SYS.foreign_keys FK

INNER JOIN SYS.OBJECTS ChildTable ON FK.parent_object_id = ChildTable.object_id

INNER JOIN SYS.OBJECTS ParentTable ON FK.referenced_object_id = ParentTable.object_id

LEFT OUTER JOIN SYS.key_constraints PK ON ParentTable.object_id = PK.parent_object_id

ORDER BY PTable

How to know SQL table Properties

SELECT O.name AS TableName, C.Name As ColumnName,T.name AS DateType,T.max_length AS [Length], T.precision AS Precesion, T.scale AS Scale,

CASE WHEN C.is_identity = 1 THEN 'Y' ELSE 'N' END AS [Identity],

CASE WHEN C.is_nullable = 1 THEN 'Y' ELSE 'N' END AS Nullable

FROM SYS.OBJECTS O

INNER JOIN SYS.columns C ON C.object_id = O.object_id

INNER JOIN SYS.types T ON T.system_type_id = C.system_type_id

WHERE TYPE = 'U' AND T.name <> 'SYSNAME'

AND O.name <>'sysdiagrams'

ORDER BY O.name, C.column_id

Simple way to Restore Database

CREATE  DATABASE TestDB
 GO

RESTORE Database TestDB
FROM
DISK= 'D:\TESTDB.BAK'
WITH  REPLACE

How to reset password in SharePoint 2010

Wherever we change our windows password after that we face problems with SharePoint
Webapplication (cannot create webapplication or many more things so we need to change the password here as well. We have many ways to do this)

1. Go in Services and change the password for all SharePoint related service account
2. Go to IIS and update the servicepool password related to SharePoint

OR

The otherway which take cares of every thing. Which is easy and quick process...

Need to execute these commands from DOS prompt
(in SharePoint folder where if got installed)
(e.g. C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN)

Go to Run: Open cmd prompt to execute following commands one by one:

stsadm -o updatefarmcredentials -userlogin DomainName\UserName -password NewPassword -local

stsadm -o updateaccountpassword -userlogin DomainName\UserName -password NewPassword -noadmin

iisreset /noforce