Recently I needed to add a Microsoft SQL 2012 R2 database to
my vRealize Orchestrator (v6.0.1) SQL Plugin (v1.1.4) inventory.
This was so I could start writing workflows to pull
reporting data from various sources (API\REST\SQL\XML\CSV) and upload into a
central reporting DB.
I had a few problems finding the correct documentation and methods,
so I made this post to help me in the future!
NB There are lots of possible config issues, so I have made the below assume that;
The server is called SQL01
The vRO server can resolve SQL01 to the correct IP
SQL TCPIP communication is enabled and on port 1433 (SQL Express will not enable it by default, and a named instance probably won't use port 1433)
The server has the one default instance
The database is called Reporting
The SQL server uses windows authentication (this may work with mixed mode too)
The account to use is a local account called svc_reporting and
has the correct DB access permissions
1 Log in to the Orchestrator client
2 Click on the Workflows icon in the top left
3 Expand Library, SQL, and Configuration
4 Right click on Add a Database and choose Start Workflow
5 Enter the following data;
Name ReportingDB
Database
Type MS SQL
Connection
URL jdbc:jtds:sqlserver://sql01:1433/Reporting;domain=epc35sql01
Session
mode Shared session
User name svc_reporting
Password <enter the
correct password>
6 Click Submit
7 Assuming the workflow completes successfully, click the
Inventory icon in the top left, and expand SQL Plug-in, then Reporting DB to
see the contents (there will be two trace maps even with a blank DB)
As I said there are lots of things that can stop this working, but the assumption cover most of them. The biggest stumbling block I had was the correct syntax for the URL - namley adding ";domain=" to prevent the connect attempt using SQL authentication (event viewer errors alerted me to that).
No comments:
Post a Comment