Home > SSIS Best Practices > SSIS – windows authentication <> personal account

SSIS – windows authentication <> personal account

I wrote a blog SSIS – How to change the default SQL Agent login. When your ETL server (where Integration Services are installed) is not the same server as your database server, you will need to create a domain service account and use that account as your default account for any SQL jobs. The blog showed you how to do that.

In order to use the default domain service account for your SSIS job, you will need to set your connection with Windows Authorization, not with a SQL account.

However this concept seems to be a little hard to understand for some developers.

A job I set up failed recently with error message like this:

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.  End Error  Error: 2011-02-20 19:00:01.79     Code: 0xC0016016     Source:       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.  …….

I exported the SSIS package and opened it up in BIDs, and realized that a developer has changed all the connections to use SQL account SA, instead of Windows Authentication.

I fixed it by changing them back to Windows Authentication.

Later I got an email from the developer.

Sherry,

If we are using windows authentication connection for all connection, I am assuming it is your windows account. After your account is disabled, how will these package run?

SA account may not be a great choice, especially if package is reading/writing on file system. Do you think we should change it to my windows account?

I don’t know why developers are so confused about the Windows Authentication. The developer wanted to use her Windows account!

I answered in this way:

Windows Auth doesn’t mean that we are using personal account.

As a matter of fact, when set it to Windows Auth, we are actually using the domain service account SVC-EAMSQLAgent. This is the domain service account we’ve set up for running SQL jobs.

It’s not a good idea to hard code any SQL account for automatic jobs.

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: