0

We recently created a windows login (domain\etluser) with read-only access to different SQL Server databases. Is there anyway developers can use that generic account to develop SSIS packages using Visual Studio? It require us to create OLEDB connections using that new account, but couldn't find an option for that

I heard that it is possible to run packages as another user or proxy from SQL Server integration services, once the development and testing is over: How to schedule SSIS package to run as something other than SQL Agent Service Account

rchacko
  • 1,965
  • 23
  • 24
  • 2
    If you're talking about developing packages in Visual Studio, you could try running visual studio as the windows user. How do you do that? Start by googling it – Nick.Mc Nov 03 '20 at 09:49
  • yes, that is an option. See reply from billinkc – rchacko Nov 19 '20 at 05:13

2 Answers2

1

In windows, I am aware of two mechanisms to start a process as another user. From the command line, you can use RunAs

As a consultant, I often bring my own device to clients and then use Runas to impersonate my client-domain account without having to get my laptop's domain trusted by their corporate domain. I have a folder filled with batch files that launch a program with their credentials. For example, this is my script to start Visual Studio 2017 Enterprise Edition as my "other" self.

runas /netonly /user:domain.com\billinkc "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE /nosplash"

I've not done SSRS development in a long while, but 2014?ish at least this approach would allow me to develop reports but I could not preview the reports within VS as the preview process would get launched as a "new" process which inherits my current credentials instead of the ones I was started the parent process under. SSIS dev though, worked just fine with this approach.

The biggest complain I have with either approach is that I have to type the password every time. You can't redirect the input from elsewhere and you don't see the typing so you can't tell how many characters you've typed if you screwed up the password. Couple that with an aggressive lockout policy and I spent a lot of time at one client twiddling my thumbs waiting for my account to unlock.

The other approach is a GUI approach. In Windows Explorer, shift right-click on the executable and you should be presented with a context menu like the following.

enter image description here

Pick "Run as different user" and you'll be prompted with a more friendly window to enter credentials

enter image description here

With Runas, you could at least prepopulate the user to save a few keystrokes but with the GUI approach, you have to enter everything every time.

billinkc
  • 59,250
  • 9
  • 102
  • 159
0

If you run your SSIS jobs through the SQL Server Agent, you can use Proxies: https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver15

There's a couple steps involved:

  • Create a user for your dom\etluser on the SQL Server, in Security \ Credentials
  • Create a proxy in the SQL Agent using those credentials, and authorize it to run SSIS jobs
  • Create a job (or edit an existing job), with the SSIS package as a step and on that step set 'Execute As' to the proxy.
steenbergh
  • 1,642
  • 3
  • 22
  • 40