Connect SSRS to PostgreSQL Database Part I

Connect SSRS to PostgreSQL Database This SQL Server Reporting Services (SSRS) Blog Series reviews how to integrate SSRS with PostgreSQL Database back-end. A month ago, I had to provide a POC in a day showing integration between SSRS and PostgreSQL Database. Here is my journey, hope this provides some help to the next person.

Topics covered, guidance around tool selection, configuration, and a couple gotchas reviewed in detail to help ease setup proof of concept. The goal is to connect SSRS to a PostgreSQL Database and demonstrate report creation, deployment and viewing. At some point, likely Part III I’d like to understand and demonstrate how deployment can be automated.

Part I covers three basic areas Assumptions, Server Configuration and starts Development Configuration. Part II will cover the remainder of Development Configuraiton, SSRS Configuration, Viewing, and Known Issues. Part III will attempt to offer discussion and couple options for Automation.

Series:

  • Connect SSRS to PostgreSQL Database Part I
  • Connect SSRS to PostgreSQL Database Part II
  • Connect SSRS to PostgreSQL Database Part III

Assumptions

The document will not cover installing Windows Server 2008 R2, Windows 7 or PostgreSQL 9.2. However, known PostgreSQL 9.2 configuration gotchas will be covered.

  1. Operating System: Windows Server 2008 R2, Windows 7
  2. SQL Server 2008 or 2012
  3. SQL Server Reporting Services ( SSRS ) 2008 R2 or 2012
  4. PostgreSQL 9.2
  5. Report Builder 3.0

1 Server Configuration

1.1 PostgreSQL Configuration

The assumption is PostgreSQL 9.2 Database is already installed. For installation guides please reference Running and Installing PostgresSQL in Native Windows or Step-by-Step Installation of PostgresSQL onto Windows Server. There are a couple configuration gotchas related to setting up PostgreSQL for access over TCP/IP as by default this feature is turned off. Use psql PostgreSQL interactive terminal or pgAdmin GUI to test connection details to the server.

Defaults:

  • Port: 5432
  • TCP/IP: disabled

1.1.1 TCIP/IP Configuration

For demo purposes default use of prot 5432 is fine. To permit development tools and SSRS server to connect to PostgreSQL Database via an ODBC connection PostgreSQL must be configured to permit a connection over TCP/IP.

1.1.1.1 Update pg_hba.conf file

To permit the SSRS server and client tools used to build the reports permission must be added to the pg_hba.config file permitting TCP/IP access. If there are several machines on the same subnet requiring access use a /5 range option DO NOT add seperate entries as this will cause the service to fail. For example, there are five IPs starting at 192.168.12.10 to 192.168.12.15 that require access. The entry below is valid.

TYPE DATABASE USER DATABASE METHOD
host all all 192.168.12.10/5 md5 |

Any changes to pg_hba.conf the PostgreSQL service require a service restarted. To restart the service open the command prompt as Administrator and execute one of the commands below based on the version and kernal installed.

1.1.1.1.1 PostgreSQL 32 bit Service Restart
  • NET START postgresql-9.2
  • NET STOP postgresql-9.2
1.1.1.1.2 PostgreSQL 64 bit Service Restart
  • NET START postgresql-x64-9.2
  • NET STOP postgresql-x64-9.2

1.2 PostgreSQL ODBC Driver Installation

1.2.1 Download Install Drivers

Download Windows Installer for PostgreSQL ODBC drivers for Windows 32 and 64 bit here and run the windows installer.On the server it is very likely all that is required is the 64 bit version. However, this assumes you are going to reference a shared ODBC connection using DSN and always force clients to deploy using that 64 bit version. Otherwise one of three things must occur:

  • Install 32 bit and 64 bit driver, create a shared ODBC connection using DSN
  • Create an SSRS shared connection to point to each ODBC DSN 32 and 64 bit and require client applications creating and deploying reports to use the same named DSN as the server locally.
  • Create an automated build and deployment to transform the DSN names to match server DSN.

1.3 PostgreSQL ODBC Connection Configuration

1.3.1 Create 64bit PostgresSQL ODBC Connection DSN

1.3.1.1 Setup DSN for 64 bit application

1.3.1.1.1 Start –> Run –> %WINDIR%\System32\odbcad32.exe

ODBC Data Source Administrator

1.3.1.1.2 Select System Tab

ODBC Data Source Administrator

1.3.1.1.3 To add a new DSN click Add

ODBC Data Source Administrator

1.3.1.1.4 Select PostgreSQL Unicode(x64) driver
  • PostgreSQL Unicode: use this if your database was set up with the UTF-8 character set.
  • PostgreSQL ANSI: use this if your database was set up with a LATIN character set.

ODBC Data Source Administrator

1.3.1.1.5 Click Finish

ODBC Data Source Administrator

1.3.1.1.6 Enter configuration details for PostgreSQL database.

To verify the connection details click Test then Save to create the DSN. The DSN should now appear in the System DSN tab.

ODBC Data Source Administrator

ODBC Data Source Administrator

1.3.2 Create 32bit PostgresSQL ODBC Connection DSN

1.3.2.1 Setup DSN for 32 bit application

1.3.2.1.1 Start –> Run –> %WINDIR%\SysWOW64\odbcad32.exe

Follow the steps outlined for 64 bit installation. The only difference between creating the DSN for PostgreSQL 32 bit ODBC driver is running the proper command above. Instal of seeing 64 bit driver in step 1.3.1.1.4 it will be a 32 bit version as seen below.

ODBC Data Source Administrator

1.4 SSRS Configuration

The assumption is SQL Server 2008 R2 is installed with SSRS and simply needs to be properly configured. If SSRS is not currently installed, please reference How to Add Features to an Instance of SQL Server 2008 R2 guidance from Microsoft. To start SSRS configuration reference SSRS Configuration section to properly configure SSRS.

1.4.1 Permissions

Site and Folder security settings must be set properly for client, non Admin users to access SSRS features. Groups and Users will not be able to access SSRS until these security settings are configured properly. For demo purposes add your local user account. For additional information and guidance concerning roles reference Using Predefined Roles.

1.4.1.1 Security Configuration

1.4.1.1.1 Site Settings Security

On the left hand menu click Security. At the top ribbon menu click New Role Assignment to add a new role. For additional information and guidance concerning roles reference Using Predefined Roles.

SSRS Site Settings

SSRS Site Settings - New Role Assignment

1.4.1.1.2 Folder Settings Security

In addition to setting system roles folder and viewing security roles must be setup too. Navigate to Home, then click Folder Settings. At the top ribbon menu click New Role Assignment to add a new role. For additional information and guidance concerning roles reference Using Predefined Roles.

SSRS Folder Settings

SSRS Folder Settings - New Role Assignment

2 Development Configuration

2.1 PostgreSQL ODBC Driver Installation

Development environment installation differs from server installation in that most of the applications used to develop SSRS reports require a 32 bit ODBC version ( SQL Server BI Development Studio, SQL Server Data Tools for VS 2012, and Excel). Install both 32 and 64 bit PostgreSQL ODBC drivers.
Report Builder 2012 ( version of Report Builder 3.0 ) uses 64 bit if SQL Server 2012 64 bit version was installed. Follow the steps outlined here.

2.2 PostgreSQL ODBC Connection Configuration

Create User DSN for applications mentioned above to reference the DSN instead of System DSN. User DSN: is available just for the current user. System DSN: is available for all users and services on the machine. Follow the steps outlined here but create User DSN instead of System DSN.

2.3 Development Selection

2.3.1 Report Builder 3.0

Microsoft SQL Server 2008 R2 Report Builder 3.0 provides an intuitive report authoring environment for business and power users. It provides the full capabilities of SQL Server 2008 R2 Report Services.

Report Builder 3.0 introduces additional visualizations including maps, sparklines and databars which can help produce new insights well beyond what can be achieved with standard tables and charts. The Report Part Gallery is also included in this release – taking self-service reporting to new heights by enabling users to re-use existing report parts as building blocks for creating new reports in a matter of minutes with a “grab and go” experience. Additionally, users will experience significant performance improvements with enhancements to the ability to use Report Builder in server mode. This allows for much faster report processing with caching of datasets on the report server when toggling between design and preview modes.

SQL Server 2008 RS Report Builder 3.0

Finding, Viewing and Managing Reports using Report Build 3.0 and SSRS

This FREE tool can be downloaded here, including a readme and samples.

Before attempting to install prodect please review the requriements below:

  • Supported operating systems: Windows 7, Windows Server 2003, Windows Server 2008, Windows Vista, Windows XP
    • Windows XP Service Pack 3
    • Windows Vista Service Pack 2
    • Windows 7
  • Note: This component also requires Microsoft .NET Framework 3.5 SP1.
  • 80 MB of available hard disk space
  • 512 MB of RAM
  • System Prerequisites for Report Builder 3.0 Sample Reports
  • Microsoft SQL Server 2008 R2 Report Builder 3.0. You can use the Report Builder 3.0 standalone version or the ClickOnce version of Report Builder 3.0 installed with Reporting Services.
  • Access to an instance of the SQL Server 2008 R2Database Engine.

2.3.2 SQL Server BI Development Studio

Business Intelligence Development Studio is Microsoft Visual Studio 2008 with additional project types that are specific to SQL Server business intelligence. Business Intelligence Development Studio is the primary environment that you will use to develop business solutions that include Analysis Services, Integration Services, and Reporting Services projects. Each project type supplies templates for creating the objects required for business intelligence solutions, and provides a variety of designers, tools, and wizards to work with the objects.

SQL Server 2008 R2 Introducing Business Intelligence Development Studio

Microsoft Visual Studio 2010 does not support Business Intelligence Development Studio Integration Services, Report Services and Analysis Services projects for SQL Server 2008 and SQL Server 2008 R2. To work around this issue, you can install Visual Studio 2008 alongside Visual Studio 2010 on the same machine and then open the Business Intelligence Development Studio projects in Visual Studio 2008.

If using VS 2008 is not appealing, yet another option is to install Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012. This tool suite will also work against a SQL Server 2008 R2 Reporting Services Instance.

SQL Server Business Intelligence Development Studio Menu item

2.3.3 Report Builder 2012

Microsoft SQL Server 2012 Report Builder provides an intuitive report authoring environment for busness and power users. It provides the full capabilities of SQL Server 2012 Report Services.

Report Builder provides data visualizations that include charts, maps, sparklines, and data bars that can help produce new insights well beyond what can be achieved with standard tables and charts. Use Report Builder to create reports and shared datasets. Publish report parts, and then browse the Report Part Gallery to reuse existing report parts as building blocks for creating new reports quickly with a “grab and go” experience.

This FREE tool can be downloaded here, including a readme and samples.

Before attempting to install prodect please review the requriements below:

  • Supported operating systems: Windows 7, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 2
    • Windows XP Service Pack 3
    • Windows Vista Service Pack 2
    • Windows 7
  • Note: This component also requires Microsoft .NET Framework 4.0.
  • 80 MB of available hard disk space
  • 512 MB of RAM
  • Microsoft® SQL Server® Report Builder for Microsoft® SQL Server® 2012. You can use the Report Builder standalone version or the ClickOnce version of Report Builder installed with Reporting Services.
  • Access to an instance of the Microsoft® SQL Server® 2012 Database Engine.

2.3.4 SQL Server Data Tools for Visual Studio 2012

If using Report Builder 2.0 or VS 2008 BI stool suite is not appealing, yet another option is to install Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012 or Report Builder 2012 ( updated Report Builder 3.0 ) . This tool suite will also work against a SQL Server 2008 R2 Reporting Services Instance.

This FREE tool can be downloaded here, including a readme and samples.

Before attempting to install prodect please review the requriements below:

  • Supported operating systems: Windows 7 Service Pack 1, Windows 8, Windows Server 2008 R2 SP1, Windows Server 2012
    • Windows 7 (x86 and x64)
    • Windows 8 (x86 and x64)
    • Windows Server 2008 R2 (x64)
    • Windows Server 2012 (x64)
  • Supported architectures:
    • 32-bit (x86)
    • 64-bit (x64)
  • Hardware requirements:
    • 1.6 GHz or faster processor
    • 1 GB of RAM (1.5 GB if running on a virtual machine)
    • 10 GB (NTFS) of available hard disk space
    • 5400 RPM hard drive
    • DirectX 9-capable video card running at 1024 x 768 or higher display resolution
  • Prerequisites:
    • Microsoft .NET Framework 4.5
    • Microsoft Visual Studio 2012 Shell (Isolated) Redistributable Package
    • Microsoft Visual Studio 2012 Shell (Integrated) Redistributable Package
    • Microsoft Visual Studio Tools for Applications 2012
    • Microsoft Report Viewer 2012 Runtime

References

Comments