How to Resolve SQLServer Access Denied in PostgreSQL

How do I fix “SQLServer access denied” when PostgreSQL connects to SQL Server?

“SQLServer access denied” indicates the server rejected your login or object-level permission when PostgreSQL (or any client) tried to connect or run a query.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What triggers the “SQLServer access denied” error?

SQL Server returns the message when the supplied login lacks CONNECT permission on the instance or required object-level rights. PostgreSQL users usually hit it through an FDW, ODBC, or ETL job.

How do I check login versus object permissions?

Start by confirming the SQL Server login exists and is mapped to a database user. Then verify the user has CONNECT, SELECT, INSERT, UPDATE, or EXECUTE as needed on target schemas, tables, or procedures.

Does the PostgreSQL role have a matching user mapping?

For tds_fdw, your local Postgres role must map to a SQL Server login with adequate permissions. Without it, every foreign table access fails with “access denied.”

How do I grant the minimum rights?

Always grant least privilege: CONNECT to the database and specific DML rights to tables such as Customers, Orders, Products, and OrderItems.

Should I use GRANT or DENY?

Prefer GRANT to allow access. Only use DENY to explicitly block a permission that would otherwise be inherited.

What is the safest workflow?

1) Create a dedicated login. 2) Add it to a low-privilege database role. 3) GRANT needed rights. 4) Map the Postgres role with CREATE USER MAPPING.

How can I audit permission issues?

Run SELECT * FROM sys.fn_my_permissions(...) for object-level checks and review SQL Server error log or Extended Events for failed login details.

Example: fixing access to the Products table

The snippet below creates a login, maps it, and grants read-only access so Postgres can query Products.

Why How to Resolve SQLServer Access Denied in PostgreSQL is important

How to Resolve SQLServer Access Denied in PostgreSQL Example Usage


-- PostgreSQL session
SELECT id, name, price
FROM   products  -- foreign table mapped to SQL Server
WHERE  stock > 0
ORDER  BY price;

How to Resolve SQLServer Access Denied in PostgreSQL Syntax


-- 1. In SQL Server
CREATE LOGIN pg_reader WITH PASSWORD = 'Zyx$9!temp';
GO
USE EcommerceDB;
CREATE USER pg_reader FOR LOGIN pg_reader;
GO
GRANT CONNECT TO pg_reader;
GRANT SELECT ON dbo.Customers    TO pg_reader;
GRANT SELECT ON dbo.Orders       TO pg_reader;
GRANT SELECT ON dbo.Products     TO pg_reader;
GRANT SELECT ON dbo.OrderItems   TO pg_reader;
GO

-- 2. In PostgreSQL
CREATE EXTENSION IF NOT EXISTS tds_fdw;
CREATE SERVER mssql_srv
  FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (servername 'sqlsrv.example.com', port '1433', database 'EcommerceDB');

CREATE USER MAPPING FOR report_role
  SERVER mssql_srv
  OPTIONS (username 'pg_reader', password 'Zyx$9!temp');

IMPORT FOREIGN SCHEMA dbo LIMIT TO (Products)
  FROM SERVER mssql_srv INTO public;

Common Mistakes

Granting permissions to the SQL Server login but forgetting to run REVOKE DENY overrides. A DENY always wins over GRANT, so remove conflicting DENY statements first.
Using Windows Authentication in the FDW while the Linux-hosted PostgreSQL server lacks Kerberos tickets. Switch to a SQL Server login or configure Kerberos properly.