ADO connection string to hit SQL Server msdb database?

Automise Discussion
1

Greetings!  I have a Perl script that selects from the SQL Server msdb database, and I am attempting to port the logic of that script into Automise.  The Perl script does not need a user ID and a password to select from the msdb database, and so I am trying to set up the ADO Iterator the same way.  Unfortunately, I cannot figure out a way to do this.  I have tried setting up the UserID and Password fields to be blank, as in:

Provider=SQLOLEDB.1;Persist Security Info=True;User ID=;Password=;Initial Catalog=msdb;Data Source=CTDB

I have also tried removing the User ID and Password fields altogether, and trying it with a User ID of sa and no password.

None of these work…any idea what I am missing?  Can an ADO connection hit that database?

Thanks,

Jonathan

2

Solved it. The correct connection string to use is:

Driver={SQL Server};Server=CTDB;Trusted_Connection=Yes;Database=msdb;

3

Hi Jonathan,

Great to hear. Glad this is now working for you.

4

Well…it is and it isn’t.  It worked when I ran the action list manually inside of Automise (likely because of the permissions I have on the server), but it failed when run as part of an automated process last night.  I assume that the user that it tries to execute under is the user that is running the Automise command-line?  I will try a tweak of that user’s permissions to see what I can do. 

5

That worked.  I gave the logged-in user permissions on the database, and it ran fine last night.