Using SQLCMD

[timjcw32767]

Our databases are rather locked down, require an OS account to have access, and access is limited to the related application's OS user. I cannot use SQL users for anything.  Given that I'm looking for a way to execute queries and stored procedures designating a different user than the Automation Engine OS user.  Does anyone use sqlcmd as an option to execute queries or stored procedures?  I'm not having much luck using sqlcmd. Is there a custom interpreter value that needs to be added? Any examples would be great.

I've considered having a unique agent that starts with a different account but would prefer to have something more versatile and not require an agent for every single database connection.

[Rick Murray]

We have a few different ways to run sql's, and are moving to a Windows based job that uses a ODBC connection to the external database.

If you want to review, just let me know

Rick

[timjcw32767]

Rick,  I would appreciate your insight.  Feel free to DM me whenever works for you.

[Markus Embacher]

Hi,

please try specifying the required user in the Connection Obcects "Advanced Settings" - Connection Properties Extension:

authenticationScheme = NTLM
domain = <your_ad_domain>
userName = <username_without_domain>
integratedSecurity = true

This should enable you to use a single SQL agent to using multiple users. The password will be taken from the login object. 

Regards, Markus