Read-write database access for Blackbaud CRM

Hosted customers in the public cloud environment now have read-write access to their Blackbaud CRM non-production data. This includes all transactional and warehouse data, including custom fields.

Benefits of Read-Write Database Access

  • Read-Write will speed up your software development life cycle by enabling you to troubleshoot, test, and customize your Blackbaud CRM.

  • You can use specifically use Blackbaud's SDK tool named Load Spec to load catalog items directly into your non-production environments.

  • Read-Write reduces the need for local development instances of Blackbaud CRM, saving you time and money.

  • With Read-Write, you can build out your Blackbaud CRM more efficiently, helping drive better outcomes for your organization.

Note: If you are an existing customer and want access to this option, contact your account manager to request it.

Consider using it to:

  • troubleshoot,

  • test,

  • and modify data

to extend and customize your Blackbaud CRM solution.

Contact your account manager to request access

To request read-write access to your non-production databases, contact your Blackbaud account manager. You must be an existing customer.

View connection credentials

Approved read-write users can view their unique credentials and database strings for connection access.

  1. Go to Administration, Tools.

  2. Then select Database Read Write Execute user 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 and usernames

Before you can connect to the read-write database, you must provide Blackbaud the IP addresses of any machines that will access it.

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

You must also provide the usernames of the specific users who will access the read-write database.

File a support ticket with Blackbaud and include the IP addresses and usernames.

Use the data

You can use LoadSpec, a tool in the Blackbaud CRM SDK, to add items directly to the catalog, as you would with a local environment. This is because LoadSpec supports a database connection string.

You can user third-party tools to troubleshoot, test, and modify data to extend and customize your Blackbaud CRM solution.

If you connect through SQL Server Management Studio (SSMS), you cannot access view definitions. However, you can access these details on the online technical reference document.

Use existing ad-hoc queries

You can copy and run existing ad-hoc queries against the read-write database.

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

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

Frequently Asked Questions

Usage

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-write 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.

Does read-write work for SSIS packages?

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

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

No.

Does the read-write database support custom fields?

Yes.

You can use it for production troubleshooting by running SQL queries directly rather than having to file a ticket.

Which IPs do I need to provide to Blackbaud?

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.

The read-write database will be associated with your public cloud non-production environments. 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-write 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. File a ticket with customer support.

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

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

Troubleshooting

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?

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

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.