Read-only database access for Blackbaud CRM

Hosted customers in the public cloud environment can directly access a read-only, live copy of their Blackbaud CRM data.

It includes all transactional and warehouse data, including custom fields.

Also known as an always-on secondary node, read-only replica is completely separate database. It replicates your production OLTP and data warehouse.

Accessing the read-only database does not impact the performance of your production database. It doesn't add any load to your production OLTP.

The read-only supports any SQL connection and enables you to access all database tables (even ones that don't appear in n Blackbaud CRM queries).

It performs must faster than OData connections and requires minimal setup.

Consider using it to:

• connect with SSMS, SSRS, BI Tools, etc.

• run SQL queries on it to help troubleshoot your production database, without needing to file a customer support ticket.

• run large queries on it without causing the performance of your production database to drag.

 

Contact your account manager to request access

To request access to a read-only copy of your database, contact your Blackbaud account managers. You must be an existing customer.

View connection credentials

Admins can view credentials for connection access.

1. From Administration, select Tools.

2. Then select Database read only access.

3. Next, under Connection strings, select Show connection strings.

   This shows the:

   • OLTP and OLAP (data warehouse) connections strings,

   • IDs,

   • and passwords.

     • Passwords expire after 60 days and are automatically reset the Sunday before they expire.

     • To continue the connection, you must update the password on your connected applications.

     • To reset the password at any other time, outside of the automatic 60 day rotation, file a Blackbaud customer support ticket to request a new password.

Provide approved IP addresses

To connect to the read-only database, provide the IP addresses of any machines that will access it.

To get these IPs, file a Blackbaud customer support ticket.

If you normally connect to your office through a VPN, consult your provider to determine if any other specific IPs must also be allowed.

Use the data

Consider connecting the read-only database to third-party business intelligence tools, such as Power BI. Encryption must be turned on.

These tools may enable you to :

• run reports,

• extract data,

• and troubleshoot production data.

Since this is a SQL native connection, you can also use tools such as SSMS, SSIS, and Microsoft Excel. You can write an SSIS package that connects to the database but you cannot install a custom SSIS package on the server that contains the read-only database.

If you connect through SSMS, you can't access view definitions. However, you can access these details on the online technical reference document.

Warning: Currently, you can't run stored functions. We've disabled them for security reasons. We will consider providing access to an approved subset of functions in the future.

Power BI gateway

If you use Power BI, and want to publish and schedule reports for end users, be sure to install and configure a BI gateway. Also remember to select the Skip test connection option.

Note: If you are using Power BI, install and configure a BI gateway if you intend to publish and schedule reports for end users and make sure “” is selected.

Use existing ad-hoc queries

Admins can copy and run existing ad-hoc queries against the read-only database.

1. On the Results tab of the ad-hoc query, view the raw SQL.

   

2. Copy and paste the SQL text to the tool you want to use.

Frequently Asked Questions

Purpose

How is this read-only database different from the environment we already have?

It is a completely separate, read-only replica of your production environment, or what is called an always-on secondary node. It is maintained in real time. You have the ability to directly connect to it.

Is this a replica of the OLTP and the data warehouse which I can connect to?

Correct. The database is a real-time, read-only replica of your production OLTP and data warehouse.

Does accessing this database add any load to my actual OLTP?

There is no load added to production OLTP when utilizing the read-only option.

Is the only difference that we can integrate this data with a BI tool? Is that not the intended function of the OData connections?

It supports any SQL connection. You can connect with SSMS, SSRS, BI tools, etc. You have complete access to all tables, not just those covered in Blackbaud CRM queries. It is high performance, much faster than OData connections, and requires minimal setup. You can use it for production troubleshooting by running SQL queries directly rather than having to file a ticket.

Usage

Can you deploy the read-only database to my local environment?

No. It is only designed to be deployed to your production environment.

Should I be cautious in running large read-only queries against my OLTP database?

You will see absolutely no impact or performance drag on your production environment when running queries against the read-only database. It is a separate database.

Are any other services or intermediaries required?

You must be running Blackbaud CRM in the public cloud environment. You must provide us the IPs for any machines that are going to access the read-only option. You will find see a task under Administration where you can access the connection strings and the user/password. Passwords are auto rotated every 60 days. We have found that if you use Power BI, you will need to install and configure a BI gateway if you want to publish and schedule reports for end users to access.

Would it be suitable to connect reports directly to this database, or should I extract data into my reporting database and run reports from there?

Both are equally suitable.

I want to initially build out my reporting with Power BI, but at some point I want to pull data into my existing enterprise data warehouse, which is using IBM Cognos BI, IBM DataStage for ETL, and running on a SQL Server database platform. With the read-only database, I would initially use it as a data source for my Power BI reports, and then connect to it as a data source to my existing data warehouse using the DataStage ETL tool. Does that seem like an appropriate approach?

It does. These sorts of possibilities are why we built this.

Does read-only work for SSIS packages?

Yes. You can write an SSIS package that connects to the read-only database. But you cannot install a custom SSIS package on the database server.

Will I lose access to OLAP read-only during an ETL refresh?

No.

Does the read-only database support custom fields?

Yes.

Which IPs do I need to provide to Blackbaud?

A: If you do not VPN to a standard IP, each person will need to be allowed access. Who needs access to the data source is very much dependent on the tool or report. Some tools pull the data locally to build the reports, others may reach directly to the data source. Also, it depends whether you are using cloud-based tools or desktop-installed tools.

If I connect through a VPN, which IP address needs to be provided to Blackbaud?

The IP address for your individual machine, and perhaps a range of IPs provided by your VPN provider.

For the group of IP addresses we want to have approved, we are currently hosted in the public cloud for our testing, staging, UAT, and production environments. We also have an on-premise environment for development. Help me determine the environment for which we should be providing IP addresses.

A:   The read-only database will be associated with your public cloud production environment. When you get onboarded, you will see a new feature in the production Administration area. There, you can see the connection string, user ID, and password to use to connect. You should request access for any machines you plan to use to connect to the read-only environment.

Do we get any kind of logic laid over the top of the transactional data to gives us views to work from?

You are able to see and select from existing database views.

Passwords

When do the passwords rotate? Can I avoid downtime with my connecting applications?

Passwords expire every 60 days, and they are automatically rotated the Sunday before expiration. Once the rotation is completed you can proceed to update your connecting application credentials with the new password. Until you do so, those applications will fail to connect because they would still be using the old expired password.

Can I request to have a permanent password?

No.

Can I update the password via an API?

No.

Can I request a password rotation on demand?

Yes, just file a ticket.

Why can I update the SFTP password via the Rotate task available on the form, but not the read-only password?

SFTP users are normal domain users. Read-only opens up permissions too much so we have automated the password reset.

Troubleshoot

Using SSMS, I can see the tables and views, but I am unable to see the view definitions. Is there a place I can go to see those?

A: Blackbaud provides an updated online technical reference document.. This includes all table and view definitions.

I have built a number of ad-hoc queries in Blackbaud CRM. Can I copy those and run them against the read-only database?

Yes. On the Results tab of the ad-hoc query, you can view the raw SQL. You can then copy/paste it to the tool you are using to connect to the read-only database. Note: If you are using Site Security and do not have administrator privileges, an administrator must copy the SQL for you. This is because the raw SQL may contain inserted SQL logic that is applying site security checks, and this extra logic references a stored function which will cause the query to fail.

The connection is down. I cannot see the read-only database.

Try checking your connection string to make sure you are looking at the right one and make sure there is not a refresh taking place of the Blackbaud Data Warehouse.

The connection is failing and it times out.

Ensure you have included the port in the connection string and that your internal firewall is not blocking the connection.

I'm receiving an error that the trigger failed.

Ensure you have encryption turned on. If you are using a PowerBI gateway, make sure the option "Skip Test Connection" is checked.

I'm getting a connection error about my password.

You must update the new password on all your connections. If one connection attempts too many logins with the old password, you will be locked out for 30 minutes.

I am not able to include existing stored functions in my SQL statement, even ones that are read-only in nature. I have to decompose the stored function to get to what I need.

For security reasons, we disabled the ability to run stored functions. In future releases, we may provide an approved subset of functions.