December 17, 2012 at 12:51 pm
Folks,
We had a pretty bad situation - everytime I would log onto the SQL 2008 server (I'm a domain admin) I'd be OK for a few minutes, and then it would blue screen. This started happening out of the blue (pun intended), with no Windows updates or changes...this also happens if I RDP to it with this profile.
We quickly realized other profiles/users were fine...so I could log on with a domain service account I use for other things, and it would not crash.
So the plan became to remove the old profile, recreate it, and hope for the best...
HERE'S THE PROBLEM: I've copied the SSIS packages from the old profile location to the new profile. SSIS will open the project file, but when I try to open the package, I get errors "Failed to decrypt protected XML node DTS:Password Key not valid for use in specified state"
Is there a way (if so how) I can open these packages while logged on as a different user than who created them???
At this point I still have the old profile, but afraid to use it since it crashes the server.
Any advice REALLY appreciated!
Thanks!
December 17, 2012 at 1:18 pm
1. Generate a config file and put the username and password within it. From the SSIS menu in BIDS.
2. Why not add the SSIS package to the SQL Instance? If you Build the package, you can then go into the bin directory and use the deployment tool to deploy the package, or you can connect via SSMS to the SSIS portion of the server and import that package. Then you can setup a job to run it for you automatically.
December 17, 2012 at 1:20 pm
dstoltz 35443 (12/17/2012)
Folks,We had a pretty bad situation - everytime I would log onto the SQL 2008 server (I'm a domain admin) I'd be OK for a few minutes, and then it would blue screen. This started happening out of the blue (pun intended), with no Windows updates or changes...this also happens if I RDP to it with this profile.
We quickly realized other profiles/users were fine...so I could log on with a domain service account I use for other things, and it would not crash.
So the plan became to remove the old profile, recreate it, and hope for the best...
HERE'S THE PROBLEM: I've copied the SSIS packages from the old profile location to the new profile. SSIS will open the project file, but when I try to open the package, I get errors "Failed to decrypt protected XML node DTS:Password Key not valid for use in specified state"
Is there a way (if so how) I can open these packages while logged on as a different user than who created them???
At this point I still have the old profile, but afraid to use it since it crashes the server.
See if this helps: http://www.fulltimedba.com/CommentView,guid,b7a50119-3555-4375-bd48-70089ef5b1a2.aspx
HTH,
Rob
December 17, 2012 at 1:33 pm
Robert - I've read that solution in a few places - but unless I'm not understanding, this would require me to be able to open the package, and change those settings...
I can't open the package - that's the problem, I can't log on with the profile that let's me open the package.
December 17, 2012 at 1:37 pm
mtassin (12/17/2012)
1. Generate a config file and put the username and password within it. From the SSIS menu in BIDS.2. Why not add the SSIS package to the SQL Instance? If you Build the package, you can then go into the bin directory and use the deployment tool to deploy the package, or you can connect via SSMS to the SSIS portion of the server and import that package. Then you can setup a job to run it for you automatically.
Hi mtassin,
I'm kind of a SSIS newbie (if you couldn't tell) 😉 - so I'm not sure how to create a config file - would I be able to do this under the "new" profile? (since I can't use the old one without crashing the server)
And yes, I have previously added the package as a job in SQL Server - so I can access the job, schedule it, etc. But I'm worried about having to change/update anything in the job at some point in the future.
December 17, 2012 at 1:46 pm
The error you have above is because BIDS/Windows won't let you open up the packages and maintain the named SQL Logins and passwords that you have saved in the connections. BIDS will clear them usernames and passwords.
So open up the packages and take the hit, refill in the usernames and passwords to your connections.
Then click on the SSIS menu, and click Package Configurations. Enable them, click add and work through the prompts. Pick each of your connections in your package and save the usernames, passwords, server, database, etc settings.
When you open the package after this the configuration file will override whatever is in the package. With the connection info in the package, the security features of BIDS will no longer prevent somebody from using the saved SQL credentials for your package. Just be sure to protect the configuration file because you'll have plaintext usernames and passwords in there.
However, it then makes your package much easier to deploy to the SQL instance, which means you can set it up as a job instead of manually running it in BIDS.
December 17, 2012 at 1:51 pm
Just FYI,
because of the pain of SQL Logins and passwords and what BIDS/SSDT does with them, I prefer to use Windows authentication. All of my connections rely on the proxies I've configured for the Service Account to use, which means that I don't have to worry about opening the packages with one account or another, it lets my developers work on the packages on the dev/staging environments, and then different proxies run the packages in production.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply