xcopy deployment to sql server DTS package in restricted environme

[Guest]

Hello.

I'm an old VB6 programmer who would like to distribute an .exe with the
simplest method possible, and that's not working.

I have an application which updates rows on a SQL Server. If I run the .exe
in my project's debug folder, it runs fine. If I run it from my project's
release folder, I get a sqlClientPermission error when it tries to update
data on the server.

I don't have administrative rights to my PC, so I'm assuming I have to live
with this or find someone with admin rights to grant me permission to run the
release version of my application on my PC.

If I run it copied to a folder on the network, I get the same error, but
what I really need is for it to run in a DTS package on our SQL Server so it
can be scheduled to run nightly.

The release .exe gets the same error in the DTS package, and I'm not sure
who needs to do what to get this to work. The debug.exe runs ok in the DTS
package as long as my sps have dbo ownership.

I'm not sure if I need to create a setup project and have the DBA install
the .exe on the sql server, have some administrator (not our DBA) grant
client access permissions to my .exe on the sql server, or some other set of
magic tricks.

Distributing applications used to be easy, but now it's harder and muddling
through documentation on code access security is frying my brain.

Please help me if you can.

Thanks for all replies,

-Beth

 

[William Vaughn]

How are you connecting? SSPI (trusted connection?)
If so, you're probably right. Because you're not in the Administrators group
you don't have sufficient rights. In this case you need to grant rights to
the group to which you belong that are sufficient to make the changes.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Guest]

Yes, I'm connecting with Integrated Security=SSPI.
Is there another way I could connect to avoid these problems?
Like have a new account created for this application?

When you say I need to grant rights to the group to which I belong, are you
talking about on the SQL Server?

Since I'm not the DBA, can you tell me what I should tell him to ask for?

Thanks for the help,

-Beth

 

[William Vaughn]

I would have a conversation about rights and privileges with your DBA. My
new book discusses the basic elements of Windows Forms and ASP.NET security
so you might take a look at Chapter 9. Basically, you either need to have
the DBA create a SQL login for the application or for the class of users
that will use the application and grant rights to that login for the
specific resources the application uses--and no more. This means the
application should only have rights to the specific stored procedures and
views you've used--but not to the base tables.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Guest]

William,
Thanks for your response. You obviously see what we're trying to do and are
up against.

This application runs a bunch of batch procedures to group data in our data
mart into smaller tables.
We created an account specific for this application with full rights (as far
as I know) but the DTS package gets the same security error with the release
..exe.

Should there be a way we can get this to work without someone with admin
rights granting client access security to the .exe on the sql server? I was
also wondering if I have to digitally sign the .exe or something.

Thanks again,

-Beth

 

[William Vaughn]

Try using Run As with the DTS package.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------