Executing rs utility.

Hi,

I have two doubts :

1. The rs Utility has 2 arguments : -u username & -p password.

Is there any way by which we can use encryption/decryption for these parameters ?

2. Is there any way by which we can specify a url which indicates the machine name where the rs utility should be executed.

As rs utility would be present only on the machine where Reporting Services are installed. A way to specify the machine name where the rs utility should be executed would be helpful.

Thanks in advance.

1. No, unless you have custom security extension that knows how to decrypt them

2. The utility takes serverURL parameter.

|||

Thanks for the replies.

1. Even if we have I have a custom security extension, the rs utility should know how to decrypt the username/password. Can you please explain in some detail of how to use custom security extension ?

2. The serverURL parameter which the utility takes is the reportServerURL. I was searching for scenario where in :The Reporting Services (rs utility) are installed on X machine and I want a way to say that execute the rs utility on the X machine from Y machine. Is there any way to do this.

|||

1. A custom security extension (aka Forms-based Authentication) allows you to replace the default SSRS Windows-based security model, as explained in more details here.

2. So, your question is how to get an exe to execute on another box when I start it locally? I don't think you can do this.

Executing password protected packages

Hi - I have a master package that executes a series of other packages. Each of these 'sub' packages has the security property Encryption Level set to 'EncryptSensitiveWithPassword'.

The master package has a series of file connections in the Connection Manager, one for each sub package, in which the password of the corresponding sub package is provided.

When I run the master package in BIDS (in interactive mode) it opens each of the sub packages, requests the password and gives the 'Document contains one or more lines of extremely long text' dialog box.

Is there any way to suppress the repeated password requests (seeing as it has already been provided in the Connection Manager) and warnings about long lines of text when executing the master package?

Thanks . . . Ed

There is no way to suppress the warnings about long lines of text - that is an annoying VS thing. Hopefully it will disappear in some upcoming service pack.

I don't know anything about the password requests thing I'm afraid.

-Jamie

|||

Hey Jamie,

What's up buddy, long time no talk to...

How have you handled this encryption piece before when working working with many packages, ever use the encrypt sensitive by password, etc...?

|||

Hiya Jason,

No, we never use encryption. We store passwords in .dtsconfig files.

I've just re-read Ed's initial post and realised why the prompt for a password is appearing. Simply its because the package has a password on it so whenever it opens up, you need to provide that password.

Ed, how are you supplying the password for each package within the connection manager?

-Jamie

|||

Ah, that makes sense, thanks...

I would recommend you do this Ed, just to eliminate the annoyances of having to enter passwords all the time.

|||

Hi Jamie

Apologies, my original post was badly worded . . . the password for the package is provided in the Execute Package task properties (the PackagePassword property).

Is this property ignored when the package is run in BIDS in interactive mode? Am I misunderstanding something?

Thanks . . . Ed

|||

Ah right. Yes, that makes more sense :)

Running this in BIDS is just a peculiarity I guess. BIDS opens up a package, notices it has a password, and asks for the password. it'd be nice if BIDS were clever enough to say "Ah, this package is being executed from elsewhere, perhaps the password is supplied there".

I suspect that this would be very difficult to do though.

-Jamie

Execute DTS Package From ASP.NET

I am trying to execute a DTS package in my asp.net code, but the dts package has both a owner and user password. I want to be able to use the user password to execute the package within the code, but whenever I try that I get the following error:
Access to package properties requires entry of package owner password.

Line 101: oPKG.LoadFromSQLServer("(local)", "UserName", "Password", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "DTSPassword", , , "CopySourceTest1")Line 102:Line 103: For Each oStep In oPKG.StepsLine 104: oStep.ExecuteInMainThread = TrueLine 105: Next

It works fine if I execute with the owner password, it just will not work with the user password. Any ideas will be appreciated
Thanks

To run DTS package through a stored proc you either use DTSRUN.exe or XP_CMDSHELL which is SQL Server Agent dependent. Try the links below for DTSRUN.exe sample code and XP_CMDSHELL configurations with permissions. What I am saying is to run DTS with SQL Server Agent dependent service like xp_CMDSHELL you must give the account used to install SQL Server Agent Admin permissions in Windows and SQL Server. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

http://www.sqlteam.com/item.asp?ItemID=19595

|||

We aren't executing this through a stored procedure. I am importing the DTS reference. Here is the code that makes it work:

Dim oPKGAs DTS.Package

Dim oStepAs DTS.Step

oPKG =New DTS.Package

Dim sMessageAsString

'Load Package

oPKG.LoadFromSQLServer("(local)", "UserName", "Password", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "PackagePassword", , , "CopySourceTest1")

ForEach oStepIn oPKG.Steps

oStep.ExecuteInMainThread =True

Next

'Execute

oPKG.Execute()

oPKG.UnInitialize()

oStep =Nothing

oPKG =Nothing

I'm trying to figure out how I can run this code or something similar without having to execute the cmdShell stored proc and the DTS run utility. My question is, is it possible to execute this code with the User password on the DTS Package instead of the Owner password?
Thanks

|||I could be wrong but I don't think you can because all automation in SQL Server is SQL Server Agent dependent. Hope this helps.|||I am pretty new to this, but I just don't understand why it would work using the owner password though. If i go to Enterprise Manager I can execute the DTS package with the user password.
Also, I have another question that is kind of off the topic, but related. If I do not impersonate my user account the package does not execute. It give me an access denied error to the database .ldf file. Why do I need to impersonate my user account to execute it. I understand when I run this I am using the ASP.NET user account, but any other time that user can write to the .ldf file, for example when logging in or anything, correct?|||The text below is from Microsoft about SQL Server Agent permissions and read the post below for more info about using DTS with stored procs in an Asp.net application. Hope this helps.
("Important: SQL Server Agent will need local Windows administrator privileges if one of the following is true:
SQL Server Agent connects to SQL Server using standard SQL Server Authentication (not recommended).
SQL Server Agent uses a multiserver administration master server (MSX) account that connects using standard SQL Server Authentication.
SQL Server Agent runs Microsoft ActiveX? script or CmdExec jobs owned by users who are not members of the sysadmin fixed server role. " )

http://forums.asp.net/906564/ShowPost.aspx
|||

We decided that the code I am using will work, we will just use the owner password. But now, Are the permissions and user accounts the same for the way I am executing this? Everything I read seems to be executing it with the stored procedure xp_cmdShell and the DTS Run utility which I am not using. I am confused on why I need to impersonate my user account (which has administrator rights on the local machine). Why can't I just use the IIS Asp.net User account to execute it? When I execute the package impersonating my user account it will write to the database .ldf file and the package will execute but when I do not impersonate my user account it does not write to the .ldf file and the package does not execute, I get the access denied error. I guess I get kind of confused on the user accounts and permissions needed.

Thanks

|||

justn_m87 wrote:

I guess I get kind of confused on the user accounts and permissions needed.

I think you are just confused about the permissions needed to run SQL Server Agent dependent services and you are not alone, Microsoft just got to accept the permissions for SQL Server Agent because their customers could not get replication in SQL Server to work following their configuration guidelines. I don't know what to tell you DTS in Asp.net with stored proc must give SQL Server Agent correct permissions. You cannot use the IIS account because so many services in SQL Server are SQL Server Agent dependent so running IIS and SQL Server Agent on the same account is just not good practice. Hope this helps.|||Hello
Could U plz help me out of this problem
The problem is while executing a Dts package from aspx page
It is giving the error "Error System.Runtime.InteropServices.COMException (0x8004040E): Invalid GUID specified"
the code is :-
package.LoadFromSQLServer("(local)", "myuser", "mypwd",DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "","DB8112F5-7279-486E-97AF-ACBC333A611A","D4573855-FDC6-46B7-B644-04058B49299F", "OutterSync", "")
I have given the servername,username, password,GUID, Version ID,packagename and there is no owner password and user password for thepackage.
It is working fine with dtsrun utility with the same guid and ver id, but it is giving error Invalid GUID from asp.net.
Thanks
vikky