Sqlcmd - run sql query script without cleartext password?

Question

Hope you're having a great day thus far :)

I'm using this script to automate an sqlquery, so that another team can use these logs. However, they are not permitted to have the login credentials for this database, and my current script uses a cleartext password.

I suggested that we create a new database profile and limit the permissions of the profile, so that only the specific table can be accessed with read-only permissions. However, this isn't best practice, and my lead doesn't like the idea.

So, I'm trying to figure out a way to pass the login credentials through the script, without using cleartext.

Do you have any ideas?

This is the current format of the cmd I'm running in the batch file:

sqlcmd -S server.database.windows.net -U user@domain -P password -d DB_Name -i "c:\users\%USERNAME%\desktop\Blue Prism Audit Logs\eventdatetime24hr.sql" -o "c:\users\%USERNAME%\desktop\Blue Prism Audit Logs\Audit Logs\queryOut%DATE:~4,2%_%DATE:~7,2%_%DATE:~-4%_%time:~0,2%%time:~3,2%.csv"

Answer 1

1. Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select 'New Job'

2. In the 'New Job' window enter the name of the job and a description on the 'General' tab.

3. Select 'Steps' on the left hand side of the window and click 'New' at the bottom.

4. In the 'Steps' window enter a step name and select the database you want the query to run against.

5. Paste in the T-SQL command you want to run into the Command window and click 'OK'.

6. Click on the 'Schedule' menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).

Click 'OK' - and that should be it.

(There are of course other options you can add - but I would say that is the bare minimum you need to get a job set up and scheduled)

example tsql code with output

DECLARE @cmd sysname, @var sysname;  
SET @var = 'Hello world';  
SET @cmd = 'echo ' + @var + ' > var_out.txt';  
EXEC master..xp_cmdshell @cmd;

more info here: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver15

this will create a script that runs whenever you want it to and you set it up on the DB and then you can decide where you put the output i.e. in a target location for the other team to pick it up.