osql security best practices
-
Morning all,
We have many remote sites running SQL Server 2000.
I have a created a stored procedure to check the status of backup jobs run on these remote servers. I want to use osql to run the stored procedure on the remote servers and capture the output to a text file. The osql batch file would be launched each morning by Task Scheduler.
I am looking for advice on how to give permissions to osql. It seems that these are the options:
1. Supply -U sa -P <password> in the batch file. I believe the sa account password can be hidden as an osqlpassword environment variable.
2. Use a trusted connection.
Any pros and cons or other ways? If a trusted connection is used, is it the account Task Shedular runs in?
Any advice greatly appreciated!
Jonathan
-
Use trusted whenever possible, most secure.
Never use sa (even if password hidden), if you have to use sql login create specific login with minimum access.
If a trusted connection is used, is it the account Task Shedular runs in? Yes.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks David,
I suspected as much about the sa account.
When I configure the task in Task Scheduler I am given the opportunity to enter the account to run the batch file under. If that account has permission to execute the stored procedure and the -E switch (trusted connection) is used in the osql call I should be good to go?
Jonathan
-
As long as the account can login into sql and as you stated has execute permissions on the proc then it should work. The best way is to try it and see.
Far away is close at hand in the images of elsewhere.
Anon. -
Works like a charm! Thanks for the help.
Jonathan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply