Solved

SSIS Connect Logon and SSIS packages only work when created on server itself.

Posted on 2013-06-03
5
344 Views
Last Modified: 2016-02-11
Background: We have 2 servers running SQL Server 2008 R2 on Windows Server 2008 R2.  I am the admin\DBA for both servers so I have the Windows "Administrator" password and the SQL  "sa" password.  The company has a Windows domain.  However, these servers are "Stand alone" - they are not a part of the domain.  A month ago we went from Win2003\ SQL2000 to this.  I'm primarily a VB developer, so go easy on me with any vague security descriptions.  I don't know if it matters, but when I'm on my PC in SSMS (as "sa") the "can" next to the server name only shows a white circle on it (no green arrow).

Where ever possible, I always connect with SQL Auth. "sa".

When I try to Connect to SSIS from my PC in SSMS, the only thing I am allowed to change is the server name.  It forces Windows Auth.  and the user name defaults to "domain\my PC user".  So when IS connects (ironically I get the green arrow on the IS in SSMS) I cannot expand out the MSDB - it gets a huge error message about "failed to retreive data for this request").

First question - I'm guessing there is no fix for this because I cannot Connect to SSIS with SQL Auth. or change the Windows username to drop the domain so I can login with user name only (SQL Server is not in the domain)?



Next situation:

I have tables that I copy from Prod. server to backup server at night.  I use to use DTS for this.  They do nothing fancy - clear destination table and then copy the entire table.  When I migrate some work, others do not.  So I am just going to recreate them by hand.  I figure the best way to do is is let the system do it so I'm using the SQL Export Wizard to build them and save them as an SSIS package (MSDB) and then schedule the Jobs to run at night.

If I run the wizard and schedule the job from my PC in SSMS, it fails at night when running - some kind of Connection errors and I think it mentions "sa".  If I "Remote Desktop" into the actual server (signed on as Windows Administrator and SSMS as "sa" (the same thing I sign onto SSMS on my PC - "sa")) and setup the SSIS package from the "local" server in SSMS and schedule the job from there, the scheduled package runs fine at night.

Second Question:  I really don't like having to "Remote Desktop" into the production server every time I want to run the Export wizard, create  an SSIS export package and schedule it.  Are there some settings or something I can do so I can do this task from my PC in SSMS?

Thanks
0
Comment
Question by:sqdperu
  • 3
5 Comments
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
You're the DBA so log on with sa and go to the Management part of the server and set your Windows ID to be an admin ID (i.e. give it all rights except denyread and denywrite, of course!). Then give yourself the rights _you_ need for whatever databases you need to administer. You might get tips from here (the first two are free).
After that you should be able to do what you need with your Windows logon.

hth

Mike
0
 

Author Comment

by:sqdperu
Comment Utility
Mike,

Thanks for the reply.  Could you be more specific when you say "Management part of the server".  Is that SQL Server, Windows, where exactly?

The user name I sign onto my PC with is also on the server in the Windows "Administrators" group.  I keep wondering if how it wants to connect to Integration Services on my PC with the forced Domain name\User Name is causing an issue because my PC is in the domain and the SQL server is not (by design).  I tried to create a Windows user on the Server for my Domain\Username, but it won't let me because it does not like the "\".

Thanks
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
Comment Utility
When running the Export Data Wizard I assume you activated the "Save SSIS Package" checkbox (otherwise I don't see how you would schedule it)?

What did you select as Package Protection Level?  By default it is set to "Encrypt sensitive data with user key".  Basically this means that connections will fail when the package is executed by another user than the one that was used to create it.  And that's what you may be seeing.

I suggest to select "Do not save sensitive data" and use package configuration to store the connection string or other sensitive parts.

More info:
Using SSIS Package Configurations
0
 

Author Comment

by:sqdperu
Comment Utility
ValentinoV,

I didn't know how to do the "configuration file" and it appeared more complex than setting it up in the Job.  Setting it in the Job seemed quicker, easier, and worked best for me.  So I created the package on my PC and save it to the SQL server with the Package protection level set to "Do not save sensitive data" like you suggested.  Then I created the Job and on the Data sources tab I checked the Destination and Source connections and added "Password=___;" to the end and provided my 'sa' password.  I scheduled the package and it ran.

Thanks for your help.

I'm including the step-by-step below in case it may help someone else out.  (At least this is what worked for me):

 How to setup a SSIS package from your PC and get it to run on the server.

1. Right-click Database and choose Tasks \ Export data...

2. Fill out the various screens taking defaults and using ‘sa’ as the user and its password.  (You may need to “Edit Mappings…” and select “Delete rows in destination table” and check “Enable Identity Insert” depending on your intent.)

3. Uncheck “Run immediately” and check “Save SSIS Package” with the “SQL Server” option.  You MUST select from the “Package Protection Level:” the drop-down choice “Do not save sensitive data”.

4. Once you have the package saved, in SSMS right-click on “Jobs” and select “New Job…”.

5. Give it a name (SSIS - …), and then click on “Step” on the left.

6. On "Steps" screen, give it a name (Step1), in “Type” drop-down choose “SQL Server Integration Services Package”, in Server box type your server name the package is saved on, change option to “Use SQL Server Authentication” and type in ‘sa’ and it’s password.  Click the “…” next to “Package:” and choose the SSIS package you saved in step 3.

7. Click on the “Data sources” tab.  Check both the DestinationConnection and the SourceConnection. Then for each, go to the end of the string and type  “Password=___;” providing the appropriate password for ‘sa’  in place of the blank.

8. You can then go to the “Command Line” tab and see that it has added your Password to the string.  Click OK to finish the “Job Step”.

9. On the left, click on “Schedules” then click “New…”, give it a name and set up the time and type of schedule.  Click OK.

10. Click OK to exit the “New Job” setup.

NOTE:  When you go back in to edit your job, you will not see the “Password” information you typed in on the “Data sources” or “Command line” tabs.  (Although you will see the additional lines added to the Command line.)  I'm guessing that it saves this information. If it doesn’t work, you know you have to type the Password string in every time you edit the job.
0
 

Author Closing Comment

by:sqdperu
Comment Utility
This gave me a start as the first half of the whole solution.  I got the same suggestion from another forum with the additional help of altering/saving the connection string in the job.  Thanks
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now