none
Running the 32 bit version of dtexec under a 64 bit machine via SQL Server Agent RRS feed

  • Question

  •  

    I am having an ongoing struggle with SSIS on the following issue. I am exporting Data using Excel destination object which will create an excel file and e-mail it to users.

     

    On my local 32 bit box, it's not a big issue, package works like a charm. So I copied my .dtsx file to production, hoping that It would work  there too. But it didn't take too long to realize that jetdb does not support 64 bit for Excel, package will fail and it actually did fail. So I was tempted to use WOW to run the package with the 32 bit dtexec under the 64 bit machine. I tried using the package via command line, and it worked...Next thing I tried was creating SQL Server Agent Job that will run my package. I had only one step, and in that step I was executing the command that recenly worked and of course that step had a type of Operating System(cmdexec) . I also ran the command under SQL Agent Service Account(because none of the other accounts were visible in the dropdown). Job is succesfully created, But when I tried to start the job, it gave me "access denied" error. So I thought that maybe I needed to change the paths from "c:\program files (x86)...." to \\server-name\c$\..., it didn't help either.

     

    So I came across some forum questions and I realized that I either needed to create proxy users that will execute the command or I will need change the protection level. But in Microsoft's support page, it says that changing the protecion level is a safer solution. So I tried to implement it, my first aim was to change the protection level to "Server Storage" and after that I was planning to deploy to msdb database in production. But unfortunately BI did not allow me to change the protection level to server storage level, because my excel destination objects will write to destination server.

     

    So I left the protection level as EncryptSensitiveWithUserKey and created a deployment utility and copied that to production. After that I installed the package to the SQL Server under msdb database. So I went back to my SQL Server Agent Job and selected the step type as sql integration services package, and selected the package source as sql server. After that I browsed for the package that I just installed under msdb database. I thought that maybe it would help, but unfortunately when I ran it, it says that package execution failed. I am thinking that It is running the package with 64 bit version of dtexec since I have no way of specifying the command for 32 bit version.

     

    So far I have this solution: I can create a windows scheduled service event to run the package from the command line every week but it is not good to have packages in two different locations on the server, it's not an elegant solution at all.

     

    I can try to create proxy users, but it is going to be the first one on the server, I am not sure how secure it will be. Plus If I change server that package resides it will fail.

     

    I definitely can not savee the package protection level to Server Storage,

     

    Does anybody have any idea on What else might solve my problem?

     

    Erinc

    Monday, November 3, 2008 9:06 PM

Answers

  • Couple of things:

    - make sure the SQL Server instance is running on a domain account, not a local account

    - You have to add the Credentials first, giving them a name under Server>Security>Credentials in SMS 2005 or 2008

     

    When you create the credential, make sure the Location option is pointing to your domain and not the local system.

     

    Tuesday, November 4, 2008 4:39 PM

All replies

  • I don;t quite get how you arrived to Package Protection Level and proxy usage as synonmous when talking about security -- they are two different pieces to any security implementation. If you can post back that article/page, I would be interested in reading it as well.

     

    I think you were initially on the right path with using the proxy to execute the package -- this is exactly how I would use it in your situation.

     

    One other question though -- why not use SSRS and build a data drive subscription that will e-mail the results to your user base? To echo your own sentiment, this would be the most "elegant" way to deliver information to users.

     

    For SSRS data driven subscriptions, reference:

    http://msdn.microsoft.com/en-us/library/ms159150(SQL.90).aspx

    and e-mail delivery: http://msdn.microsoft.com/en-us/library/ms160334(SQL.90).aspx

     

    Monday, November 3, 2008 10:18 PM
  • Thanks a lot Sid;

     

    I'll take a look at the link that you've sent. Here's the link from MS Knowledge Base:

     

    http://support.microsoft.com/kb/918760/en-us

     

    In this page proxy users and protecion levels have been used as possible solutions in the same context. I brought up the security because I am not sure if that's gonna bring any pros and cons on a production environment securitywise and I am not an expert on sql server security. It does not say anything about security, the only disadvantage that it's mentioning is here: "method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account. "  

     

    Thanks

     

    Erinc

     

    Monday, November 3, 2008 10:33 PM
  • Gotcha -- that line is referencing how to secure sensitive information in the package -- EncryptSensitiveWithUserKey is the combination of current user and current computer.

     

    We frequently use EncryptSensitiveWithPassword in many of our implementations and the DBAs maintain that password list inside CryptKeeper or some other password management program and our second favorite is package configuration files. For us, these two options allow the easiest level of deployment between servers, with the package configuration being the easier of the two.

     

    Monday, November 3, 2008 11:14 PM
  • The reason that I didn't go for creating the proxy user was, I am having problems when I try to enter the credentials. Because when I browse neither I see any credentials there nor I can verify my username with the coorrect domain name, do I have to install something else to the server?

     

    Erinc

     

    Tuesday, November 4, 2008 2:55 PM
  • Couple of things:

    - make sure the SQL Server instance is running on a domain account, not a local account

    - You have to add the Credentials first, giving them a name under Server>Security>Credentials in SMS 2005 or 2008

     

    When you create the credential, make sure the Location option is pointing to your domain and not the local system.

     

    Tuesday, November 4, 2008 4:39 PM