0

We are migrating our intranet to SharePoint and our previous system used postgresql so I'm trying to make it work with SharePoint, which is not easy.

I tried to add a data source on SharePoint designer and installed ODBC (psqlODBC) and OLEDB (PGNP) drivers but even with that I get errors on SharePoint designer (images below).

Also the funny part is that on the SharePoint server I'm able to connect through excel using the OLEDB provider I installed. enter image description here

enter image description here

enter image description here

The error I get when looking into SharePoint logs are listed below and happen during two POSTS (the domain for security reasons is not that one)(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
out_sid3r
  • 101
  • 2
  • Just to be clear: Do you have SQL Server installed and just try to make an additional DB connection to get the data from the old DB or are you trying to make Sharepoint to work with PostgreSQL instead of SQL Server? – Sven May 23 '16 at 11:30
  • I'm trying to make Sharepoint to work with PostgreSQL, we are considering the migration to sharepoint but we would like to keep using the PostgreSQL where all our data is saved. – out_sid3r May 23 '16 at 12:11
  • I am not a Sharepoint expert at all, but I am 99.999% sure that this will not work ever. MS explicitly [requires](https://technet.microsoft.com/library/cc262485.aspx#section4) SQL Server and at the very best, they will have made no effort to support anything else. – Sven May 23 '16 at 12:16
  • How can I be sure there is no way to achieve this ? I mean I could ask Micrososft but I would need to go through their payed support right? Also, what would be the best workaround for this? Export all data to SQL Server? – out_sid3r May 23 '16 at 12:20
  • As I said, I am no Sharepoint expert. However, from a general IT management perspective, it's really ill-advised to even try to do this. MS only supports SQL Server and even if you somehow manage to get this to work, there is no guarantee at all you will not have major issues come next patch day or even three years down the road due to some small issue you didn't even noticed until then... – Sven May 23 '16 at 12:28
  • Thanks for the feedback, in your opinion what would be the best approach to migrate my postgresql data to SQL Server so I can use it in Sharepoint? – out_sid3r May 23 '16 at 12:38
  • @Sven I don't really believe that. SharePoint _requires_ SQL Server, because _SharePoint_ stores _its_ content in SQL Server. That's not the same thing at all as trying to make SharePoint access data from another provider (as is obvious from OP's own answer). Given the errors shown in the question, I'd say MS _is_ supposed to support other drivers, but is failing to do so because of some error in either SharePoint or the PostGre driver. It's an important question to me, because I acccess data from PostGreSQL in our existing CMS, and the company wants to move to SharePoint. – Auspex Oct 15 '21 at 12:58

1 Answers1

0

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:

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.
out_sid3r
  • 101
  • 2