Progress DataDirect’s PostgreSQL ODBC Driver offers a high-performing, secure and reliable connectivity solution, for ODBC applications to access PostgreSQL data. If you want to connect PostgreSQL data from SSLS, Progress DataDirect has you covered. In this tutorial, you will learn how to configure the DataDirect PostgreSQL driver within SSMS (SQL Server Management Studio). These steps can also be used to configure any ODBC connection.
SQL Server Management Studio is an ideal environment for managing the infrastructure of the SQL Server database and SQL Server itself. With SSMS, you can easily connect to your SQL Server instance, configure and monitor database objects, as well as execute queries. Besides this, there is a possibility to create an ODBC connection to external data from SSMS.
Thus, you can work with other databases and clouds used by your applications.
In this article, we will describe a step-by-step procedure for establishing a connection to PostgreSQL from SSMS using our ODBC Driver for PostgreSQL.
2. After the DataDirect Snowflake ODBC driver has been installed, open ODBC Administrator to configure the connection. Go to System DSN and click on Add.
3. Choose the “DataDirect X.X PostgreSQL Wire Protocol” and click on “Finish.”
4. The ODBC PostgreSQL Wire Protocol Driver Setup window will pop up.
Enter your credentials, click on “Apply” or “OK,” and then click on “Test Connect.”
5. The PostgreSQL Logon screen will pop up. Provide logon credentials and click on “OK.”
6. A dialog box will confirm that the connection is established.
1. Launch SQL Server Management Studio.
2. Navigate to Server Objects > Linked Servers > Providers > MSDASQL > and confirm that the Allow inprocess and non-transacted updates options are Enabled.
3. In the object explorer navigate to Server Objects > Linked Servers. Right-click and select New Linked Server.
4. Fill in all the details.
5. Click on the Security page and select “Be made using this security context”. Then enter your PostgreSQL username and password.
6. Click on the Server Options tab and ensure that they match the following options shown in the following screen capture.
7. That’s it! You’re all done! Now you can find PostgreSQL 1 in the provider's list. Navigate to PostgreSQL 1 > Catalogs > default > Tables. You should see your tables now and be able to query your PostgreSQL data from the linked server