| Author |
Topic |
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
 Posted - 2013-12-06 : 12:20:50
|
I have a package with a connection to a SQL Server where the authentication is SQL Server auth. Say that it's called "ConnSQL".In the connection, I enter the server name, click SQL Server Authentication and enter the username and password for the server. Then I choose the database I want and check the "Save Password" checkbox. I click Test Connection and it connects fine. I click OK.Next. I open Package Configurations and create a SQL-Server based configuration for my connection ConnSQL. In the properties window I check Server, Initial Catalog, Username and Password, then Finish the entry.Now I want to test my new connection using the package configuration. So, I create a SQL task, with the query "Select top 1 * from MyTable" and point it at my connection ConnSQL.I execute that task and it fails, complaining about authorization to the database. Just to be sure, I jump into SSMS and the same account is able to acess the same database just fine. So, that's not the problem.Then, I go back to the connection definition for ConnSQL. Remember that I said I checked the "Save Password" box? Well, the box is still checked, but the password is gone! I'm guessing that's why the SQL Task failed.Question: Have I messed up the order in which I set this up in SSIS? I did:1. Create a connection called ConnSQL with SQL Server Authentication. I check the Save Password box and Test Connection works fine2. Create a package configuration for the new connection, checking the Server, Initial Catalog, Username and Password as properties to save. I Finish the entry and close the configuration manager3. Create an Execute SQL Task with a simple select: "Select top 1 * from MyTable"4. I attempt to execute the tasks but it fails with the error: quote:
[Execute SQL Task] Error: Failed to acquire connection "ConnSQL". Connection may not be configured correctly or you may not have the right permissions on this connection.
What am I doing wrong? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-12-06 : 13:13:52
|
I have to admit I've never saved a SQL config with a package before. What is the ProtectionLevel of the package? If you save it with the package I think you need to run the package with the /Decrypt parameter.Here is a link to an article that might help:http://msdn.microsoft.com/en-us/library/dd440760(v=sql.100).aspxquote:
The second method is to change the package ProtectionLevel property setting to EncryptSensitiveWithPassword, in Business Intelligence Development Studio.You access the package ProtectionLevel property by clicking anywhere in the package control flow, and then selecting ProtectionLevel in the Properties window. Next, you modify the SQL Server Agent job step command line to include the password that decrypts the sensitive data. You add the password using the /Decrypt parameter of the dtexec command prompt utility. The SQL Server Agent job steps use the dtexec utility to run packages.
When I'm using the older style of Package Deployments, I tesnd use external config files. You need to enable them and configure them, but it avoids having to store the password in the package. Although, it has the side-affect of keeping the password in plain text in a file. |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2013-12-06 : 14:50:57
|
quote:
Originally posted by Lamprey
I have to admit I've never saved a SQL config with a package before. What is the ProtectionLevel of the package?
I had it set to default (Don't save sensitive information) -- clearly wrong on my part! quote:
If you save it with the package I think you need to run the package with the /Decrypt parameter.
I'm just trying to get it going in development. I get the error when I start a Debug run quote:
Here is a link to an article that might help:[snip]
it did help me understand better. Thanks for that! quote:
You access the package ProtectionLevel property by clicking anywhere in the package control flow, and then selecting ProtectionLevel in the Properties window.
I set it to EncryptSensitiveWithPassword, but it didn't help. It doesn't save my SQL user's password either in the Connection manager OR in the package configuration |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-12-06 : 15:29:34
|
| Did you set the PackagePassword property? It should be a couple above the ProtectionLevel property. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2013-12-06 : 16:39:19
|
quote:
Originally posted by Lamprey
Did you set the PackagePassword property? It should be a couple above the ProtectionLevel property.
Yup! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-07 : 08:33:30
|
| Did you identify the SQLuser password as a configuration item? then it will get saved in your config repository (either XML file or sqlserver table depending on the option you choose)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2013-12-09 : 09:13:47
|
quote:
Originally posted by visakh16
Did you identify the SQLuser password as a configuration item? then it will get saved in your config repository (either XML file or sqlserver table depending on the option you choose)
Yes, I did. As far as I can see, this feature doesn't work. I just made a very simple test on the SQL Server instance on my local machine:1. in SSMS Create a new login "SQLAuth" with non-expiring password "SQLAuth"2. in SSIS, create a new connection manager, LocalHost.SQLAuth pointing to the local server, using SQL Server authentication and the login "SQLAuth" I just created. 3. Entered the password "SQLAuth" and hit test connection. It worked. I check "Save Password" and click OK.4. Drag and drop a new Execute SQL Task from the tool box to the design surfce.5. Select the connection manager I just created in step 2 and enter the SQL Command "Select 1"6. Save the new task and execute it. It executes fine.7. Open my new connection manager again. The password is NOT saved! This in spite of the fact I checked "EncryptSensitiveWithUserKey" in the package properties. To confirm, hit Test Connection again. It fails!Now, to test the package configurations:8. Enter the password again and click Test Connection again. It works. Click OK.9. Execute the task created above once more. It works10. Open Package Configurations and set up a configuration for my new connection. Check the Server, Userid and Password boxes for the new connection for the properties to save.11. Execute the task created above again. It fails with the message: quote:
[Execute SQL Task] Error: Failed to acquire connection "LocalHost.SQLAuth". Connection may not be configured correctly or you may not have the right permissions on this connection.
12. Go back to the Package Configurations13. Change my new config (from step 10) to save the the connection string.14. Try the new task again. It fails the same way15. Remove the new packagage config and retry. It works!At this point, I've played with this for hours without success. Using a Package Configuration with SQL Server Authentication does not work, at least not for me |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2013-12-09 : 11:25:57
|
quote:
Originally posted by visakh16
seehttp://blogs.msdn.com/b/runeetv/archive/2009/12/22/ssis-package-using-sql-authentication-and-dontsavesensitive-as-protectionlevel.aspx
that was interesting though not about using SQL Server-based configs per se. What I DID glean from the posting is that I need to manually enter the password in the configuration table in SQL Server. That is, I needed to open SSMS, find the table tb_SSIS_Configurations and edit it, then find the matching config and type in the password in the row where PackagePath = "\Package.Connections[LocalHost.SQLAuth].Properties[Password]" Now, I'm rereading the docs to see if that is specifically mentioned. Certainly that crucial step is not mentioned here:[url]http://msdn.microsoft.com/en-us/library/cc671625.aspx[/url] or here:[url]http://msdn.microsoft.com/en-us/library/cc671628.aspx[/url]or here:[url]http://technet.microsoft.com/en-us/library/ms141132.aspx[/url]The main references I used for building my package configs |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-09 : 13:31:01
|
| ok...so has your issue been solved now?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
package configuration not remembering password