Can someone tell me what the provider string looks like when trying to connect from MS SSMS to PostgreSQL database via linked server?
-
Created DSN like here sqlshack.com/configure-odbc-drivers-for-postgresql Connection successful. However, when using that DSN in MS SSMS for creating linked server, iz says wrong password?! WierdHrvoje T– Hrvoje T2023-08-03 22:54:01 +00:00Commented Aug 3, 2023 at 22:54
Add a comment
|
1 Answer
Created DSN (data source name) with ODBC Data Source Administrator (64-bit) tool. Named it PostgreSQLW31. In it, I used PostgreSQL Unicode(x64) driver and data like username, password, port, database, server. Tested connection with a test connection button. I used that PostgreSQLW31 data source name instead of the provider string in SSMS. You can use one or the other.
After that in SSMS, right click on server link created earlier, serurity, and then add Local server login to remote server login mappings. Add local SQL user, add remote db user and pass and it works.
link for casting not supported data types
Edit: I succeeded to connect with this data provider (connection string): Provider=MSDASQL; DRIVER=PostgreSQL Unicode(x64); SERVER=my_server_ip; DATABASE=my_db; UID=my_user; PWD=my_pass
-
As a side note: ConnectionStrings.com is a wonderful collection of all kinds of DB connections.vonPryz– vonPryz2023-08-08 06:22:42 +00:00Commented Aug 8, 2023 at 6:22