QNAP/Linux Tool - Access PostgreSQL from SSMS (SQL Server Management Studio)

If you are familiar to use Microsoft SQL Server, you should be appreciated by the easy access among servers and cross database provided by SQL Server. Especially access all servers' databases through SSMS (SQL Server Management Studio) environment. Fortunately, accessing PostgreSQL from SSMS is simple and convenient.

After install QNAP/PostgreSQL, the sa (system administrator) account and default password are "postgres". Start PostgreSQL service from QNAP App and open phpPgAdmin with "postgres/postgres", set a secured password first.

• SQL: alter role postgres with password ‘***’

Set password of "postgres", the checkbox must be disabled due to no row returned.

Keep a good manner to develop Web Apps based on database services, you should use different dbusers for accessing different databases. Especially, don't use/write sa or postgres accounts in web programs. Therefore, I create a new dbuser named "nas" to access a new database.

• create user nas with password '123'
• drop user nas   -- delete the dbuser

Then create database name "nasdb" for "nas" with UTF8 encoding.

• create database nasdb encoding 'UTF8' owner nas

Using SQL code "create table" to create tables and insert data rows for testing.

To access PostgreSQL from SQL Server Management Studio (SSMS), you must download the newest PostgreSQL ODBC version and install it. Then open ODBC Manager from Control Panel/System and Security/System Management/ODBC Data Source (64-bit or 32-bit depends on your windows platform).

• Tip 1: Right-click the ODBC icon, and send it to desktop or pin it into taskbar.
• Tip 2: For Win8 users, just type "odbc" in the Metro Home UI, you will find it more easily.

Click "system data source name (DSN)" to set a DSN can be used by other Apps.

Select correct ODBC driver: PostgreSQL Unicode(x64) for my Win8 64-bit platform.

Type Data Source name "xxx" and PostgreSQL server/db/user/pwd settings.

If you see the message, set the pg_hab.conf to open accessing right for your Window host IP.

I commented the line so that I cannot connect pgsql outside localhost.

Remove the comment "#" and try again.

Now, you are ready to connect to PostgreSQL. Open SSMS and find "Linked Servers".

Linked Servers (Database Engine) - MSDN - Microsoft: Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.

To access an outside DB Server from SSMS, you must add a new Linked Server Name as the representation.

Right-click / New

Set "PGS" as the Linked Server Name

The name PGS represents the ODBC Data Source name "nasdb".

Then you can browse the allowable database (depends on the ODBC's setting database and account) within the server.

Browse the Linked Server and check accessible tables.

Realize the namespace of PostgreSQL to access PostgreSQL database from SSMS.

• Namespace: server.db.schema.table
• SQL: select current_schema

In PhpPgAdmin, select the target PostgreSQL database "nasdb", and check the schema name.

Get rows of PostgreSQL DB from SSMS with Transact-SQL statements.

Simple, easy and convenient to access PostgreSQL from Microsoft SSMS.