none
SharePoint and Postgresql database RRS feed

  • Question

  • We are exploring the option of migrating our current intranet to SharePoint, the issue is that all of our data is on a PostgreSQL database and I have no idea how to make it accessible for Lists in SharePoint.

    I tried adding a data source using the SharePoint Designer 2013 but didn't work, as such how can I access/manage a PostgreSQL database from SharePoint ?

    Friday, May 20, 2016 4:58 PM

Answers

  • This is how I fixed the issue.

    Sharepoint won't give the option to select the appropriated external data source driver unless you install layer2solutions (despite the above replies):

    www.layer2solutions.com (this is not free)

    This will give you a new link called "Connect to external data source" on all list settings page which then allows you to select the the ".Net data provider for Postegresql" and using the below connection string you are able to connect to PostgreSQL.

    User ID=user;Password=password;Host=postgresql_host_ip;Port=5432;Database=database_name;

    As the data provider I used npgsql:

    https://github.com/npgsql/Npgsql/releases

    (download the msi installer).

    1. Install npgsql
    2. Install layer2solutions
    3. Add a list in sharepoint
    4. Go to list settings
    5. Click "Connect external data source"
    6. Use the connection string with your DB settings
    7. Save the settings
    8. In order to create new List Items in Sharepoint the ID (primary key) in Postgresql table needs to have a default serial value otherwise you'll get a "not null" error.
    • Edited by outsiderPT Monday, May 30, 2016 9:38 AM
    • Marked as answer by outsiderPT Monday, May 30, 2016 9:38 AM
    Monday, May 30, 2016 9:36 AM

All replies

  • You would need to install a PSQL provider on your client in order to consume it from an External Content Type as well as the SharePoint server(s). On SharePoint, the provider must be 64-bit.

    Trevor Seward

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Friday, May 20, 2016 5:26 PM
    Moderator
  • Thanks for the quick reply, but could you provide a little more details, what do you mean with the "provider on your client"? What client exactly? Should I install it both in the postgresql database server and in the SharePoint server?

    Do you have any recommended PSQL provider?

    Friday, May 20, 2016 5:35 PM
  • A provider is a driver you install on computers that need to connect to a data source type (or server, such as PSQL). Microsoft has built-in providers for Microsoft SQL Server, but not 3rd parties like MySQL or PSQL.

    Here is a document, while it talks about Power Query, that is applicable to your scenario:

    https://support.office.com/en-us/article/Connect-to-a-PostgreSQL-database-Power-Query-bf941e52-066f-4911-a41f-2493c39e69e4?ui=en-US&rs=en-US&ad=US&fromAR=1

    PostgreSQL also has a list of providers, some pay-for, others free located at http://www.postgresql.org/download/products/2-drivers-and-interfaces/.


    Trevor Seward

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Friday, May 20, 2016 5:39 PM
    Moderator
  • Thanks again.

    Do you mean if I follow the 

    "Install the PostgreSQL .Net Data Provider"

    Section of the link you sent on the sharepoint server I'll be able to fetch the data from postgresql? By the way where can I in sharepoint define the external datasource and use the PSQL data provider?


    Friday, May 20, 2016 5:46 PM
  • This is what I tried:

    1. I installed psqlodb driver: https://odbc.postgresql.org/
    2. Went to SharePoint designer and tried to add a new Database connection using a connection string: https://www.connectionstrings.com/postgresql-odbc-driver-psqlodbc/
    3. Got an error (image below)

    Saturday, May 21, 2016 10:38 PM
  • Doesn't look like you installed the appropriate driver (or bitness of the driver). It should appear in the Providers drop down list.

    Trevor Seward

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Sunday, May 22, 2016 2:23 AM
    Moderator
  • What driver should I install? Any recommended links?

    Also when I look into sharepoint logs I have the following entry:

    (POST:https://sharepoint.com/_vti_bin/webpartpages.asmx:GetXmlDataFromDataSource)

    05/21/2016 18:08:13.24  w3wp.exe (0x2714)   0x3D04  SharePoint Foundation   Claims Authentication   a1n25   High    Token is for a windows account. e3827e9d-0814-1066-3883-8cc161d0f500
    05/21/2016 18:08:13.26  w3wp.exe (0x2714)   0x3D04  SharePoint Foundation   Claims Authentication   a5rig   High    User key is not an encoded claim. UserKey: '            '.  e3827e9d-0814-1066-3883-8cc161d0f500

    • Edited by outsiderPT Sunday, May 22, 2016 10:04 AM
    Sunday, May 22, 2016 9:32 AM
  • Please read through the previous links for the providers you can choose from. You must install the correct bitness of the provider for SharePoint Designer (either 32bit or 64bit) and the 64bit provider on the SharePoint servers.

    Trevor Seward

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Sunday, May 22, 2016 7:34 PM
    Moderator
  • I've done what it says on the link, I've installed the .dll along with changing the machine.config file. The sharepoint server and designer are on the same machine just to keep it simple.

    Also I'm able to fetch the DB in excel using OLEDB option but not through sharepoint, I not only installed the Npgsql through the installer but I also did the .dll steps.

    Any ideas ? Just to be sure I should have option on the sharepoint designer to select the Postgresql OLEDB option right?


    • Edited by outsiderPT Sunday, May 22, 2016 8:43 PM
    Sunday, May 22, 2016 8:38 PM
  • This is how I fixed the issue.

    Sharepoint won't give the option to select the appropriated external data source driver unless you install layer2solutions (despite the above replies):

    www.layer2solutions.com (this is not free)

    This will give you a new link called "Connect to external data source" on all list settings page which then allows you to select the the ".Net data provider for Postegresql" and using the below connection string you are able to connect to PostgreSQL.

    User ID=user;Password=password;Host=postgresql_host_ip;Port=5432;Database=database_name;

    As the data provider I used npgsql:

    https://github.com/npgsql/Npgsql/releases

    (download the msi installer).

    1. Install npgsql
    2. Install layer2solutions
    3. Add a list in sharepoint
    4. Go to list settings
    5. Click "Connect external data source"
    6. Use the connection string with your DB settings
    7. Save the settings
    8. In order to create new List Items in Sharepoint the ID (primary key) in Postgresql table needs to have a default serial value otherwise you'll get a "not null" error.
    • Edited by outsiderPT Monday, May 30, 2016 9:38 AM
    • Marked as answer by outsiderPT Monday, May 30, 2016 9:38 AM
    Monday, May 30, 2016 9:36 AM
  • Hi outsiderPT,

    Thanks for your sharing.

    It will be helpful to others who encounter the same problem.

    Best Regards,

    CY


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, May 30, 2016 9:40 AM
    Moderator