I've created a DTS package with 2 connections to 2 servers. The operation transfers a db in server1 to server2. I have to set the sa password for each of the server (when setting up the connections).
Our policy is to change the server password regularly. This creates problem as I have to change the password setting at each of the connections. If there are 100 connections, I have to change it 100 times. Is there a way to avoid this so that we can avoid this tedious work.
TIA
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?
Hi, I was wondering if it is possible to change the password on a SQL Server 2000 DTS package programatically instead of justmanually using the the interface? I have over 150 DTS packages used for building BI data cubes and our password policy requires a password change every 60 days. I would like to write a windows vb.net application that loops through all the DTS packages and changes the password. Please advise. Thanks Chris
Hi, I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank. I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
How do I force IS to save password on any ProtectionLevel ?
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 an DB2 source and SQL destination. I have 2 ConnectionManager. One for DB2 (IBM DB2) and other for SQL. Both are of the OleDB type.
Well. I setup my connections and it work fine. Now i want to change my ConnectionManager for others sources or destinations. And I don't want to have to change the connection in more than forty packages.
For this, i have 2 XML configuration file in package configurations. One for connection. In this XML i have the connectionstring, user and password. These files are obtained in all the packages.
Well. If i change the connectionstring in this XML file configuration, only work fine the OLEDB for SQL. The conection for DB2 return the fail: "The AcquireConnection method call to the connection manager "_packagename_" failed with error code 0xC0202009."
I have proven to modify protectionLevel of the package to DontSaveSensitive. SQL work fine. DB2 fails. I have changed to EncryptSensitiveWithPassword. The same.
Why the packages obtain the data of connection of the configuration file for SQL and it does not work or with the configuration file for DB2.
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.
I am using the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider to import data via query (in a tranformation task) to a SQL2000 server within a DTS package.
Works great! However, the provider does not save my (iSeries) password although I choose this option. I really need the password to be saved so that I can schedule the DTS package... I don't want to have to login and run the package each time (even IT guys take vacations). Or at least I don't want to have to login everytime the server is restarted...
I am using the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider to import data via query (in a tranformation task) to a SQL 2000 server within a DTS package.
Works great! However, the DTS package(?) does not save my (iSeries) password although I choose this option. I really need the password to be saved so that I can schedule the DTS package... I don't want to have to login and run the package each time (even IT guys take vacations). Or at least I don't want to have to login everytime the server is restarted...
Perhaps I am ignorant, but, I can not find any configuration setting within DTS that will allow me to save the password for the IBMDASQL OLE DB provider. Nor have I been able to find a suitable solution to this by using the keywords search string of "package configuration" within the MSDN forums.
As a workaround, I can use the an ODBC connection along with a DSN file to save the username and password. However, I do not want this information sitting out there in plain text. I would like to use the OLE DB provider and store the username and password on the SQL server as the properties dialog says that I should be able to. Or I can define a UDL (still text file).[Or I can use an ODBC connection set up as a system DSN - I want to use the OLE DB provider]
I've read about adding a connection string to .Net's machine.config file - but, I do not want to use the .NET OLE DB provider and, again, I do not wish to store username and password information in plain text.
Is this a bug in as much as how SQL Server 2000 uses the IBMDASQL OLE DB provider?
I've called and spoke to an IBM tech in regard to this and was informed that the dialog box and error regarding the password are not IBM generated, but rather MS generated.
I am writing an ASP.NET intranet application on a Windows XP machine running IIS 5.1 which executes a MS SQL Server 2000 DTS package as part of the process. The application allows the user to select a UNC path of an input source (.txt) file to be procesed by the DTS pacakge and the UNC file path property is then set in the source object name of the DTS package tasks before executing the DTS package. Whenever I run the application using a file path that is on my development machine (the same machine that the web server is on) it works fine. When I select a source file path that is on a network share folder, the DTS package fails with the following error:Event Type: ErrorEvent Source: DataTransformationServicesEvent Category: NoneEvent ID: 81Date: 2/14/2007Time: 12:58:06 PMUser: N/AComputer: PHILSDELL2Description:The execution of the following DTS Package failed: Error Source: Microsoft Data Transformation Services (DTS) PackageError Description:Package failed because Step 'DTSStep_DTSDataPumpTask_2' failed.Error code: 80040428Error Help File:sqldts80.hlpError Help Context ID:700Package Name: ATMRecon_ImportElanACHTxtPackage Description: (null)Package ID: {3A8CC31D-A81F-40B7-BE57-AEB3AA238088}Package Version: {B01420A7-ED22-49A7-B4C7-9FA1732394E3}Package Execution Lineage: {2A9D45E8-35F8-4F0A-8339-7E5E29DA08B7}Executed On: PHILSDELL2Executed By: webaccess23Execution Started: 2/14/2007 12:58:05 PMExecution Completed: 2/14/2007 12:58:06 PMTotal Execution Time: 1.047 secondsPackage Steps execution information:Step 'DTSStep_DTSExecuteSQLTask_1' succeededStep Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:05 PMTotal Step Execution Time: 0.047 secondsProgress count in Step: 0Step 'DTSStep_DTSExecuteSQLTask_2' succeededStep Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:05 PMTotal Step Execution Time: 0.047 secondsProgress count in Step: 0Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft Data Transformation Services Flat File Rowset ProviderStep Error Description:Error opening datafile: Logon failure: unknown user name or bad password.Step Error code: 80004005Step Error Help File:DTSFFile.hlpStep Error Help Context ID:0Step Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:06 PMTotal Step Execution Time: 1 secondsProgress count in Step: 0Step 'DTSStep_DTSDataPumpTask_2' failedStep Error Source: Microsoft Data Transformation Services Flat File Rowset ProviderStep Error Description:Error opening datafile: Logon failure: unknown user name or bad password.Step Error code: 80004005Step Error Help File:DTSFFile.hlpStep Error Help Context ID:0Step Execution Started: 2/14/2007 12:58:05 PMStep Execution Completed: 2/14/2007 12:58:05 PMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0 When I check the Security Event Log on the server on which the network share is located, I find the following set of messages repeated multiple times:Event Type: Failure AuditEvent Source: SecurityEvent Category: Account Logon Event ID: 680Date: 2/14/2007Time: 1:02:48 PMUser: NT AUTHORITYSYSTEMComputer: FS1ADescription:Logon attempt by: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Logon account: ASPNET Source Workstation: PHILSDELL2 Error Code: 0xC000006AEvent Type: Failure AuditEvent Source: SecurityEvent Category: Logon/Logoff Event ID: 529Date: 2/14/2007Time: 1:02:48 PMUser: NT AUTHORITYSYSTEMComputer: FS1ADescription:Logon Failure: Reason: Unknown user name or bad password User Name: ASPNET Domain: PHILSDELL2 Logon Type: 3 Logon Process: NtLmSsp Authentication Package: NTLM Workstation Name: PHILSDELL2 Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.120.4.61 Source Port: 0The problem appears to be that the DTS package is trying to access the input source file using the local ASPNET Id from PHILSDELL2 which does not have authority to the network share. The ASP.NET application is using impersonation, set up through the web.config file: <identity impersonate="True" userName="registry:HKLMSOFTWARESkylightATMReconidentityASPNET_SETREG,userName" password="registry:HKLMSOFTWARESkylightATMReconidentityASPNET_SETREG,password" />The application can sucessfully access the network share under the impersonated id (the application first verifies the existience of the source files before executing the DTS package). I have wracked my brain trying to determine where the ASPNET security context is coming from to no avail. Can someone shed some light on what security credentials the DTS package is using to access the source files and how to change them?
We are migrating the SQL Server database from 2000 to 2012 and part of this exercise we are migrating the DTS Package to SSIS Package. we were unable to convert the password protected DTS Package to SSIS Package. Â The DTS Package created in early 2000 and we don't have a password for the DTS Package.Â
Is there a way to remove the password or read the content of the DTS Package?
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 deployed a package on mssql-server. How can I save passwords for OLEDB connections of the package? I want to launch the package from server agent according to the schedule, but I don't know how can I save passwords for connectons?
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
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
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'm using a shared data source to connect an Oracle server in my packages. After changing the database user password in the shared data source, I noticed the package concerned would fail with the following description.
I have a package (PackageA) with an Execute Package Task that execs PackageB. When I run PackageA I get this error on the Execute Package Task :
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.
PackageB has 'EncryptSensitiveWithUserKey' ProtectionLevel. I'm providing passwords in the dtsConfig so I'm guessing I should change it to 'DontSaveSensitive'?
Interestingly, PackageA also has 'EncryptSensitiveWithUserKey' ProtectionLevel, but I don't get an error about PackageA, just on the task that runs PackageB.
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've built SSIS package and made a job to execute it automatically but it always returns an error. The job returns OK but when we looked at the Log File viewer, it conatins this error log :
Key not valid for use in specified state
Failed to decrypt protected XML node "DTS Password" with error
What's wrong with the package ? Thanks in advance.
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
I have connected to Database using my credentials by checking remember password option. After few days I forgot my password. How can I recover the password as SQL remember it. Is there any way to recover my password instead of resetting it.
I am receiving the following error message when attempting to create a new SQL Authenticated login id.
Password validation failed. The password does not meet the requirements of the password filter DLL. (Microsoft SQL Server, Error: 15119)
I have four servers all running SQL Server 2005 SP2 on Windows 2003 Ent. SP1. Of the four servers, only one received the above error message using the same TSQL below.
CREATE LOGIN TEST_LOGIN WITH PASSWORD = 'pvif9dal' MUST_CHANGE, CHECK_EXPIRATION = ON
All four servers are in the same domain, which if I understand correctly, the password policies are therefore inherited at the OS level by the domain. The password being used is within the password policies of the domain.
I tried to install an ALLDATA database which run with SQL Server 2005 express edition. The data base fails to install becase of the following code that come up which is related to AS password requirement. The error that come up is:
TITLE: Microsoft SQL Server 2005 Setup ------------------------------
The sa password must meet SQL Server password policy requirements. For strong password guidelines, see Authentication Mode, in SQL Server Books Online.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=setup.rll&EvtID=28001&EvtType=sqlca%5csqlcax.cpp%40SAPasswordPolicyCheck%40SAPasswordPolicyCheck%40x6d61
------------------------------ BUTTONS:
&Retry Cancel ------------------------------
I am trying to install this database in a network server operating under Windows Server 2003 R2 with SP2. If anyone knows how to solve this problem, please let me.
--Table 1 "Employee" CREATE TABLE [MyCompany].[Employee]( [EmployeeGID] [int] IDENTITY(1,1) NOT NULL, [BranchFID] [int] NOT NULL, [FirstName] [varchar](50) NOT NULL, [MiddleName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmployeeGID] ) GO ALTER TABLE [MyCompany].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID] FOREIGN KEY([BranchFID]) REFERENCES [myCompany].[Branch] ([BranchGID]) GO ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch" CREATE TABLE [Mycompany].[Branch]( [BranchGID] [int] IDENTITY(1,1) NOT NULL, [BranchName] [varchar](50) NOT NULL, [City] [varchar](50) NOT NULL, [ManagerFID] [int] NOT NULL, CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED ( [BranchGID] ) GO ALTER TABLE [MyCompany].[Branch] WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID] FOREIGN KEY([ManagerFID]) REFERENCES [MyCompany].[Employee] ([EmployeeGID]) GO ALTER TABLE [MyCompany].[Branch] CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID --generated IDs = GID Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee WHERE EmployeeGID= 39
Well this might look like a very basic error: I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the REFERENCE constraint "FK_Branch_ManagerFID". The conflict occurred in database "MyDatabase", table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
On the login prompt for Report Server, there is a checkbox option to "Remember my Password." I check this, login successfully... but when returning to the Report Server, I am again prompted for the password, although the user name is saved. No matter how many times I do this, the password will not save in IE7. I have tried this on 3 different computers with IE7. In IE 6, I do not even get the checkbox as an option, just the user name/password prompt. In Firefox, the password saves fine. Any ideas what would be causing this?
I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this:
PHP Code:
$sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")";
Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.
Is there a format function in MDX which will help me solve the following scenario:
One of my measures is Net Sales and I have tried all different formats in Analysis Services so that it shows without the decimal places i.e. instead of showing the Net Sales for any selected combination of Dimensions as 123,456.04, I want to show it as 123,456
When I browse the cube (in Analysis Services), I can meet my requirement. However, in my front-end (Microsoft Data Analyzer), I am still getting it as 123,456.04 (even when the same in Analysis services, i.e. when I browse the cube, is being shown as 123,456).
I do not have much choice at the moment and am stuck with Microsoft Data Analyzer and unfortunately have not been able to solve this :(
Can someone think of a solution/workaround/use of MDX which will help me get the results being displayed without the decimal. Is there a format function that I can use in MDX and how to use it???
Hello,What is uniqueidentifier as a data type?Also what is the data type for setting unique STRINGS ((nchar,nvarchar), for example to be used for emails and user names in a userregistration system).SQL Server does not allow me set primary keys for columns where datatypes are not INT.Thanks in advance.
