getting Access denied error
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "PGNP" for linked server "PGNP_SAMPLES" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "PGNP" for linked server "PGNP_SAMPLES".
I saw in a few other posts to double check the AllowInProcess, and I can't seem to find that setting when looking at the properties for the linked server, but I did run the EXEC master.dbo.sp_MSset_oledb_prop N'PGNP.1', N'AllowInProcess', 1 statement several times. There was also another post saying to modify the pg_hba.conf to make sure the postgres server is allowing access. I have updated the file with the following line:
local all all md5
I have the following setup: Windows 7 box, both Postgres and SQL Server databases on same Windows 7 box, Postgres 8.4, SQL Server 2008 R2
If connecting to localhost, you would not need to modify pg_hba.conf.
After you tested connection using udl file, proceed to the linked server. Please read Developer's Manual on linked servers: pgoledb.com/index.php?option=com ... wnload&cid =3&Itemid=68.