SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with SSIS Package Configurations and SQL Server Authentication


Problem with SSIS Package Configurations and SQL Server Authentication

Author
Message
gbritton1
gbritton1
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 864
SSIS on SQL Server 2008 R2

I am trying to get SSIS Package Configuration working with a conection using SQL Server Authentication instead of Windows Authentication. As far as I can see, this does not work.

Steps to recreate problem (using instance of SQL Server on my own 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 works
10. 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:
[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 Configurations
13. Change my new config (from step 10) to save the the connection string.
14. Try the new task again. It fails the same way
15. 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
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27265 Visits: 13268
Step 7 is normal. Once you open the connection editor, the password is removed.

Which configuration did you use in step 10? Remember that the password is not saved to the configuration (if it would, it would be really use to hack that connection). You have to go to the configuration and replace ***** with the actual password.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
gbritton1
gbritton1
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 864
Koen Verbeeck (12/9/2013)
Step 7 is normal. Once you open the connection editor, the password is removed.

Which configuration did you use in step 10? Remember that the password is not saved to the configuration (if it would, it would be really use to hack that connection). You have to go to the configuration and replace ***** with the actual password.


Which config? The one I set up for my test. However, your comment

"Remember that the password is not saved to the configuration...You have to go to the configuration and replace ***** with the actual password"

Was an eye-opener. In all the reading and googling I had done to this point, I never saw that simple requirement.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27265 Visits: 13268
Gerald.Britton 28181 (12/9/2013)
Koen Verbeeck (12/9/2013)
Step 7 is normal. Once you open the connection editor, the password is removed.

Which configuration did you use in step 10? Remember that the password is not saved to the configuration (if it would, it would be really use to hack that connection). You have to go to the configuration and replace ***** with the actual password.


Which config? The one I set up for my test.


Allo me to rephrase: which type of config? XML, SQL Server config table?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search