Connection Passwords With And Without SSIS Package Configurations - Password Not Sticking
May 8, 2008
I'm learning SSIS, and just started to use SSIS Package Configurations. I want to be able to switch between a dev and prod instance of our database.
I did not specify the password in the Configuration file (XML), figuring it would get it from the package itself. (That will be the same between dev and prod). But once I set up to use the Configuration, I started to get a "Login failed..." message. I added the password to the configuration, and it's the same. I quit using the configuration file at all, and then it works again.
This may be related, but when I show the properties on my connection, it shows stars for the password. When I go to the editor, the password field is blank, and if I test the connection, it fails. I type in the password, and then the connection works. I have the "Save my password" checked, but every time I go back, the password is blank. In fact, if I type in the password for the connection in the editor, and then go to the "All" page, the password is blank. It may be a red herring, but it sure looks like it's not really storing the password, and thus the Configuration file can't connect when it tries to get it. I can type in the password, test the connection (it works), close the editor dialog, open the editor dialog, test the connection again, and since the password is now blank, the connection fails.
What do I have to do to make that password actually stick!?
Without the Configuration file, when I save the package to SQLServer, it will run as a job, in spite of the password appearing to not stick. With the configuration file, it's not even running while still in Studio.
Here are at least some of the version information, which may or may not be useful.
Microsoft Visual Studio 2005
Version 8.0.50727.42
Microsoft .NET Framework
Version 2.0.50727
Installed Edition: Professional
Microsoft SQL Server Analysis Services Designer
Version 9.00.3042.00
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00
Microsoft SQL Server Reporting Services Designers
Version 9.00.3042.00
SQL Prompt 3.5
Thank you for any help you can provide.
-thursday's geek
I'm new to SQL 2005. I am setting up some SSIS packages which will connect to an Oracle database and copy some tables from it. These packages will then be scheduled to run on a daily basis. Because they will run automatically, it is required that passwords be saved along with the connection string. However, even though the password is saved (and encrypted, I checked the .dtsx in notepad), when I run the package, the connection to Oracle fails. Only if I respecify the password does it run correctly. How can I correctly save this password so that I can schedule automatic execution? Thanks for any info.
Both the OLE DB Connection and ADO.Net Connection in SSIS Package does not remember password.
Im connecting to a SQL Server 2000 box using its sa password as test.
The SSIS package runs fine when you first set up the connection in bids
The bottom line is that SSIS keeps forgetting the password I feed into the two Connections that I'm using. I double-click a connection, type the password in, check "Save my password" and hit "OK" but the password disappears from there whenever I run the package or double-click the connection again.
is there any known workaround for this issue as I would like to schedule my SSIS package using a SSIS Step in a SQL Server 2005 Agent job.
the only thing I found when googling this error was link below but the workaround described here is a little harsh
After I had transfered SSIS package to another PC I tried to open package and I got an error. ".. Failed to decrypt protected XML node "PackagePassword" with error . ". It turned out, that the password wasn't saved in a OLEDB connection manager of the Package. However "save password" options checked! But password field was empty.
As soos as I enter password the package executes successfully.
Question: How can I transfer a password for OLEDB Connection within a SSIS package to another developer's VS2005?
I've been planning on putting all my connection strings for my SSIS packages into package configurations. So far, I've been using XML config files, and these strip out the passwords in connection strings which then have to be retyped every time in notepad.
Has anyone got some better guidance regarding this? Could I use another type of package config to avoid this problem?
My contraints are such that I cannot use integrated security for the packages, so the password MUST be in the connection string. However, I can place the config files in a secure location where only the package and admins have access to them.
I have a package that executes several other packages. I have an XML packageconfiguration file to set the servername for the parent package, do I need to just add that packageconfiguration to the parent package and the servername in the XML file will be used until all the packages executed from the parent package are executed or do I need to add that configuration file to each lower level package?
I created my SSIS package with connections to server databases. When I move the SSIS file to another machine it loses the password that is save in the connection. Is this a security feature? Is there a way to make it keep the passwords?
I am using package configurations to hold an email address. I can happily change the email adress when teh config is in an XML file, but when I choose SQL Server to store the package configurations I can change the email address but the changes are not picked up despite coming out of dev studio and back in again - it picks up the default set on the variables tab.
It simply refuses to pick up the email address from the SQL table, but happily creates the dbo.[SSIS Configurations] table with correct entries in the wizard 1st time round.
I am having a strange problem in that when ever I create a connection to an OLE db source and use SQL Server authorization and save the password, the connection manager seems to "forget" the password. That is, when I click the 'save password' check box, and do a test connection, it connects fine. But as soon as I close that connection window, and reopen it, the password box is empty, and the 'save password' box is still checked. Even if I do a test connection at this point, it wont connect because it does not have the password.
I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.
The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)
Config1 - Xml configuration file - for storing the database connection string. Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1. Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,
However, the package doesn€™t get the configuration settings from the database when I try to execute it as a SQL Agent job. There aren€™t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables. It works ok, if I change Config2 to be of type XML configuration file.
There could be two problems: 1. SQL server agent doesn€™t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn€™t seem to work.
2. SQL server agent doesn€™t get the configurations in the specified order. In my case, it could try to read Config2 first, but at that moment it doesn€™t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.
I have two SSIS projects each with different packages. I have setup the packages with configurations stored in a SQL Server table in MSDB. When I create the configuration on the second project it overwrites all of the first projects configurations. Is there a way to to get two different project configurations stored in the same SQL Server table? Any help would be greatly appreciated. Thanks!
After moving my deployment folder to the Target Server, I run the Installation Wizard. As I move next, I am missing the window which is supposed to allow me to set package config values as stated in MSDN:
"If the package includes configurations, you can edit updatable configurations by updating values in the Value list on the Configure Packages page."
Does anyone know why I am not seeing it? In my deployment bundle which I have moved over has currently 3 files:
1) SSIS Deployment Manifest
2) SSIS Package
3) SSIS Config File
Again, I double click on SSIS Deployment Manifest, and it starts fine. I go thru the steps for File System Deployment, and then it prompts for installation folder path. After that, it takes me directly to validation. Why is it not showing me the Configure Packages Page as described in the MSDN Documentation. Please advise. Thanks.
how do i add a password (ie, what is the exact wording) to the CommandLine in a SQL Agent Job that is running an SSIS package from the File System, pls?
Here is the CommandLine as I currently have it:
/FILE "\dbase01c$SSISPackageDeployment vrscanus_process_uploadscanus_process_upload.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
I have tried /PWD and /pwd and /PASSWORD already. For these, SQL Job says they are not valid commandLine parameters.
i have a few ssis packages which are scheduled as sql server agent jobs. now after a reboot of the server all the packages jobs don't work anymore. i had to reenter all the passwords for the connection manager in order to make them run again. ist there a way to store the passwords so that the keep available even after a reboot? because it's necessary to restart the server for maintenance resasons.
Something a little wride mysterious is happening with my package when I deploy it to run at the SSIS server instance. Everytime that I try to deploy it (from my local development environment) to SSIS server, my package is not keeping its database user and password.
As the database user and password are the same one we dont need to use the XML setting to keep these data there.
So, does anyone know what could be happening with my package and/or my deployment?
I'm having a problem with saving a password in a SSIS package that is accessing an Oracle database as an external data source. Â I experienced the problem that many people have had with the SSIS package not saving the Oracle password as expected. Â I successfully saved the Oracle datasource password (Password #1) using the "Encrypt sensitive with password" security setting and then setting a separate/new password (Password #2) for the SSIS package. Â I've imported this package into my Integration Services installation, and I can get it to run successfully by manually entering Password #2 when I open the package.
Now I want to pull the SSIS package into a SQL Job Agent, along with some other steps, to run on an automatic schedule. Â My question -- how/where do I embed Password #2 (the one for the SSIS package) in the settings for the SQL Job Agent step that I use to run the SSIS package? Â The SQL Job Agent step settings box prompts me for Password #2 before it will let me edit settings for the package, but it doesn't save that password. Â So when I actually run the whole job, the SSIS package always fails.
Hello, I need to run SSIS packages with the 32 bit dtexec version, and I am storing these files into the MSDB package store, in SQL Server 2005. I have chosen to encrypt sensitive data with password, and then to execute the packages with xp_cmdshell. The problem is that I receive the following message when I try to run it from management studio (when running this, I am logged in with the sa user): exec master..xp_cmdshell 'dtexec /Ser ServerName /SQL "TestFolderPackage" /De "testPass"' €śMicrosoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. NULL Started: 9:41:29 AM Error: 2008-05-22 09:41:29.70 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSassword" 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€? If I run the same package with the same command specified above, but without sensitive data in it (a simpler version with no passwords and connection strings, it works fine) exec master..xp_cmdshell 'dtexec /Ser ServerName /SQL "TestFolderPackage"' If I run the firs package version (with sensitive data and password protected) from the command line, everything works well: DTExec.exe /ServerName OLAP /SQL "TestFolderPackage" /De "testPass" I know in ASP.NET when encrypting web.config section, the system stores the decryption keys in a app data folder, and in order to be able to read from the encrypted web.config, the user under which runs the ASP.NET must be granted access to that folder, by running asp_regiis.exe with some parameters. I believe here a have a similar problem, with users not being granted access to encryption keys. Thanks in advance
Hi all, I need one more help! we can select the dtsconfig file with the Environment variable(indirect configuration); but i need to select the configuration file at runtime; i've to load the package from the server and apply local configuration file to the package and run in a web server;
Requirements; 1. i have packages with its xml configurations for connection strings alone! 2. i deployed it on the server 3. Trying to execute the packages in a web page onclick event;
We have ASP.net WebPage; in On_Click Event, i have this code;
Application ap = new Application(); Package pk = ap.LoadFromSqlServer("\PROJECT", "itsssqldb", "pmo_package_user", "password", null); pk.ImportConfigurationFile(@"Packagesdev_staging.dtsConfig"); pk.Execute(); here PROJECT is the Package name; i'm loading the package from the Common Sequel Server 2005; and applying the configuration file to that package and trying to execute this; but its not using the config file what i mentioned; its returning failure!!
Can you please help me to resolve this issue!,
(The intention of doing this job is to select the configuration file( prod, dev) at runtime)
I create a connection to an OLE db source and use SQL Server authorization and save the password, the connection manager seems to "forget" the password. That is, when I click the 'save password' check box, and do a test connection, it connects fine. But as soon as I close that connection window, and reopen it, the password box is empty, and the 'save password' box is still checked.Â
While using the connection manager name in the SSIS component (say script task) the connection is failing . As a workaround, the whole connection string has been put in a variable and used that variable in the SCRIPT task.
Is it a bug or some other property need to be set to use ConnectionManager Name .
I now have two SSIS package, "TESTING" and "LOADING". The "TESTING" package have an execute package task that call the "LOADING" package. When I want to execute the TESTING package, how can I setup the connection string so that I can edit the password of the database connected by the "LOADING" package?
In BI Tool SSIS Packages run fine and get data From Oracle and Save it in SQL Server.
Package Protection Level is EncryptSensitivewithPassword.
In BI tool when i open the package it ask password and then run fine.
If i change the Protection Level to Dont save Sensitive,
It does not run fine in even BI tool.
It is fine if i use EncryptSensitivewithPassword.in BI Tool and run it.
Now the problem is that i need to run this package through SQL Job.
so Job give error
"Failed to decrypt an encrypted XML node because the password was not specified or not correct. Package load will attempt to continue without the encrypted information."
hi in my package, some sql operations need the special user name and admin privilage. so how do i create my ssis package so that when it executes it takes the given username and password from the table in some database.
I have a package protected by a password - I am already unhappy that to get it to use the configuration file to change connection strings for the production servers I have had to hardcode the password into the config file - very insecure! However, the package now deploys correctly to the production server and will run from there OK, but NOT if scheduled as a SQL Server Agent Job. Thus is because however often I edit the command line to include the password after the DECRYPT switch (which it has prompted me for when I click on the command line tab), the Job Step will not retain it. If I open it up after I have edited it and closed it, the password has disappeared.
I know that if I run dtexec plus the code in the Command Line tab (with the password), the package runs OK.
This is driving me insane! I have read all the other posts and so I tried replacing the SSIS package step with a CmdExec step and pasting that code into there - then I get an OLEDB error..
The code I use is: DTEXEC /SQL "ImportRateMonitoringTables" /SERVER servername /DECRYPT password /CONFIGFILE "D:Microsoft SQL ServerSSISDeploymentsRateMonitoringImportTasksDeploymentImportRateMonitoringTables_Production.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
and I get
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF
although the same code executes perfectly from a command prompt.
Please does anyone have any experience with a similar problem and if so, how did you get round it?
I have a question about Package Configurations. I had developed a prototype that has connections defined to 2 DEV SQL Servers and runs fine in DEV. I created a Configuration XML file and changed the XML file settings to point to our 2 TEST SQL Servers. I ran this package without issues. However, I was expecting the 2 SQL Server OLE DB connections within BIDS to point to the TEST Server after the package ran. I still was pointing to DEV. Am I missing something here? Thanks in advance.
I have a package configuration for my SSIS packages to allow me to dynamically change the connection string of the connections in my packages. This was done so that when I deploy my packages to my development or test server, the packages would use either the development or test server name in the connection string. I have set the configuration up to use an environment variable to store the location of the config file (an xml config file). The package however does not seem to be using the environment variable though. If I change the location of the config file in the variable's value, it still points to the old location. Can someone please help.
Before I started using package configurations, I made several (wrong) assumptions. My primary use for configurations are to move packages between environments (dev, test, prod), so my config files only contain connection information.
My first assumption was I could create 3 different configuration files (XML) with the connection string properties for each environment. Because I use shared data source connections in my packages, these are the only properties I should have to store within my configuration file. Then I use indirect configurations, which I had a system environment variable set up on each server, called SSISConfigPath. On all servers, I have it pointing to a shared folder on our network so us developers can update the configuration files at will, if needed.
Now, in theory if I deploy all of my packages to the SQL Server MSDB on each of the servers, and schedule a job on each server, everything should work, right? Wrong.
First of all, we had to restart the SQL Agent service in order for the package to recognize there is a new system variable (similar to the Indirect Configuration "gotcha" that Jamie blogged about, but in this case, it wasn't a cmd window, but a windows service).
Next, an SSIS package doesn't first read the configuration file, but it tries to validate the connections that were originally stored in the package to begin with before replacing them. This is a problem when the environment you develop on is your local PC (laptop for example), and deploy to production, the SSIS package tries to connect to your laptop PC first, throwing an error because it cannot find it. If it can find your laptop, it succeeds, and only then switches the connection info with what is in the configuration file.
Can this "validation" step be disabled? It seems changing the "Delay Validation" = true on the package doesn't work for connections.
And when you open BIDs to run a package, the "Syncronize Connection Strings" dialog always appears. When you say OK, it doesn't stick and always asks you each time you open the file.
Please let me know what I should be doing, because this doesn't seem to work as flawlessly as I thought it should.
Within the SSIS package i am currently developing I have a number of variables relating to folder locations. I have added these to a SQL configuration package as i'd like to be able to change these prior to the SSIS package being executed. I plan to launch the package by calling a SQL Agent job from a web page.
On this web page i'd like to be able to change the values in the SQL database so that the package runs with User specified folder locations.
If anyone has done this before i'd appreciate if you could suggest how best to accomplish this. At present i'm thinking of just interogating the created database but as there are no keys etc set up this may not make updating the table easy.
I have an SSIS Package that utilizes two Configurations at design time...
1. Environment Variable Configuration sets the root folder variable and subsequently several other variables within the package using expressions (adapted from Jamie Thompson's template package - thanks Jamie)
2. Indirect XML Configuration to set the connection string to the appropriate db.
I've got 3 environments in play.
1. My workstation, using BIDS to design / debug the package. For my PC, the Indirect Config sets the connection to our test server/database.
2. A test server. For the test server, the Indirect Config on the test server sets the connection string to the test database on that server.
3. A production server. For the production server, the Indirect config on the prod server sets the connection string to the prod database on that server.
Problem...
Running the package in BIDS correctly runs against the test db, no problems.
Running the package on the test server in SSMS (either in the Execute Package Utility or using the Agent) without specifying any Configurations on the Configuration tabs, runs fine against the test db.
Running the package on the prod server in SSMS (again, in the Execute Package Utility or using the Agent) without specifying any Configurations on the Configurations tabs runs the package against the test db.
Is this because I'm not specifying the Configuration I have stored on the production server to use the connection string for production?
Curiously, the Indirect Configuration I have setup to read an environment variable to set the root folder (for logging, etc) works fine without having to specify any configurations on the configuration tabs in SSMS. In fact, I don't think you can specify environment variable configurations on these tabs - they open a Browse window to search the filesystem for a config file.
Any clarification anyone can give me regarding these Configuration tabs, indirect configurations, and what their purpose is would be great. I've spent days researching, reading through the two books I have, etc, and there's not much out there on this.
I created an xml configuration file and specified all the details,i have saved the package with protection level as "dont save sensitive".We are running the package thru autosys.But the package is repeatedly failing,not sure whats wrong with this one.
I have just set up my first deployment utility for the Integration services package i've been building. I seem to be getting problems with the package Configurations. I added an XML config file so that i could change the values of my SQL connection manager at deployment time. This was so that i could deploy it on both a test environment and the live environment. Any other variables can be changed in code by the calling application. As soon as i added the options for the sql connection manager and enabled package configuration i got errors when running the application:
[Execute SQL Task] Error: Failed to acquire connection "InternalProductionData Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection.
This is before i even deploy the project. If i disable the package configurations everything works as expected. Can anyone help suggest why this might not be working.
I'm relatively new to SSIS, but I have read all the info/threads on Package Config. and I'm really confused.
Our production env. is clustered, and we are running multiple sql instances, so the sys admin does not want me to use Env. Variables or XML files for the package configurations (it's too hard for him to maintain the info across multiple instances, etc). So, I am storing the package configurations in SQL.
Just as a simple test, I used the wizard to create the 1 package configuration for production. The configured value as a complete connection string
When I run the package, it completely ignores the package configuration. How do I change the connections on my tasks to read from the package configuration instead of the connection manager they are currently associated with?
