sql server security problem...

michal554253


Hi All!

I have created a package, and now want to deploy it as a sql agent job.

I have:
-Set the security protectionlevel to "don't save sensitive"
-saved a copy of the package to the sql server (using sql server authentication with 'sa' username and password)
-Created a new job on the sql server
- name is 'test'
- owner is sql1
-added a step
-type is Integration services package
-Run as agent service account (can't change this)
-package source is Sql Server
-server is the same SQL server
-log on to the server as 'sa' username and password
-package points to the package I created earlier
-No other configuration


When I try and run it, it tells me that the 'sa' password is incorrect. When I look into the datasources tab of the step, the connection string to one of my connection manager has the sa username, but no password.

when I go back to the package is BIDS, and look at the connection manager for that server, the password is blank.

When I go back to the sql job, the step (which I set up to have SQL server authetication for the package source), is now suddenly back to windows authentication.

Can anybody explain this
I get various error messages when I fiddle about with the settings:

"login failed for user 'sa'." (I know the password is right, but maybe it falls over whne it blanks out )

"Failed to decrypt protected XML node "DTSassword" with error 0x8009000B "Key not valid for use in specified state.". (I don't have a key )

any help MASSIVELY appreciated...

michal

Phil Brammer


"Don't save sensitive" is not what you want.

When saving to SQL Server (File->Save Copy As), choose "SQL Server Roles and Storage" for the protection level. That works for me. Otherwise, choose EncryptSensitiveWithPassword and specify a password in the Agent step.

michal

sweet deal. that worked on my mini test package.

i'll give it a bash with the main package

thanks for that!