I recently had to connect a Business objects-universe to an Azure databas using ODBC.
In short, the following steps needs to be taken. Each step will be explained more thoroughly below.
- Set up the ODBC data source on BO server
- Set up the ODBC data source on the client/development machine
- Set up connection in Universe Design Tool
- (Optional) Add access rights in Business Objects
Step 1: Set up the ODBC data source on BO server
- On the server where your Business Objects is installed, set up an ODBC data source. You need to add ODBC 64-bit data source, which is located at %windir%\system32\odbcad32.exe
- Select “System DSN” and “Add”
- Select your driver. In the case of an Azure database, select “SQL Server”
- Set your data source name and Server. Format for Azure server address is “tcp:SERVERNAME.database.windows.net”
- Select the way you wish to connect to the database. Most likely you will use an SQL authentication, as shown in the picture. There is currently no default database setting for Azure accounts, so you do not want to connect to SQL server to get default settings. (If you use default settings, you will only connect to “master” database!)
- Change the default database to the name of the database you wish to connect to
- Click “Finish”
- If you try to test your data source now you will get an error. This is normal, as you have no credentials entered on the ODBC data source. For now, just press “OK”.
Step 2: Set up the ODBC data source on BO client
- On the client/development machine where your Business Objects Information Design Tool is installed, set up an ODBC data source. You need to add ODBC 32-bit data source, which is located at SYSTEMDRIVE:\Windows\SysWOW64\odbcad32.exe (Example: C:\Windows\SysWOW64\odbcad32.exe)
Remember you will need to do this on ALL client/development machines where you want to use the connection!
- Follow steps 2-8 in section above
Step 3: Set up connection in Universe Design Tool
- In “Repository Resources”, right-click “Connections” and select “Insert Relational Connection”
- Enter a connection name
- Select driver. In this case, you can choose Microsoft / MS SQL Server 2008 / ODBC Drivers
- Enter database SQL authentication user name and password.
Enter the data source name (the name you entered in section 1 and 2, step 4)
You can test your connection if you want
- When you are done, click finish. Your connection is now ready to use in universes.
Step 4: (Optional) Add access rights in Business Objects
- This sections is here as a reminder: Remember to add access rights to the connection in Business Objects, if there are any specific groups that need to use the connection.
- Go to BO Central Management Console / Connections.
Right click on your newly created connection and select “User Security”
- Here you can see which users and groups have access to the connection. If you want to add more users or groups, click “Add principals”.