Linked server connection error when using a domainaccount

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Linked server connection error when using a domainaccount

Postby goosvanbeek » Wed Oct 02, 2013 5:34 pm

Hi,

Using PGNP as linked server works fine as long as the SQL Service service uses the LocalSystem account. When I change the account to a domain account with local admin rights, PGNP can't connect to the database anymore. I'm getting the following error messages:

OLE DB provider "PGNP" for linked server "PPM" returned message "Cannot open DB connection.".
OLE DB provider "PGNP" for linked server "PPM" returned message "FATAL: too many connections for role "dbiuser"

I'm using SQL Server 2005 SP2 on a Windows 2003 server; The Postgres server is a third party server to which I've no other access than by OLEDB/ODBC. See the linked server properties below.

Any ideas someone?

Regards,
Goos van beek.

EXEC master.dbo.sp_MSset_oledb_prop N'PGNP.1', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'PGNP.1', N'DynamicParameters', 1

EXEC master.dbo.sp_addlinkedserver @server = @serverName, @srvproduct=N'PGNP', @provider=N'PGNP', @datasrc=N'tpr1',
@provstr=N'PORT=5500;CNV_SPECIAL_FLTVAL=ON;COMMAND_TIMEOUT=3000;SSL=Allow', @catalog=N'pnmonsrv'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@serverName,@useself=N'False',@locallogin=NULL,@rmtuser=N'dbiuser',@rmtpassword='#######'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'connect timeout', @optvalue=N'120'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'query timeout', @optvalue=N'300'
EXEC master.dbo.sp_serveroption @server=@serverName, @optname=N'use remote collation', @optvalue=N'true'
goosvanbeek
 
Posts: 3
Joined: Wed Oct 02, 2013 4:38 pm

Re: Linked server connection error when using a domainaccoun

Postby Moderator » Wed Oct 09, 2013 3:48 am

The issue can be caused by SSPI. Make sure that linked server is using Postgres login credentials.
Method 1. In the Linked Server Properties->Security choose "Be made using this security context", and specify "Remote Login" and "With Password".
Method 2. Execute stored procedure to add the remote login, e.g.:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSVR',@useself=N'False',@locallogin=NULL,@rmtuser=N'postgres',@rmtpassword='12345'
User avatar
Moderator
Site Admin
 
Posts: 279
Joined: Wed Oct 29, 2008 11:27 pm

Re: Linked server connection error when using a domainaccoun

Postby goosvanbeek » Wed Oct 09, 2013 6:50 am

Dear Moderator,

Thanks for your reply.
Unfortunately your suggestion didn't solve the problem. (The linked server was already setup this way, but I forgot to mention it).

Best regards,
Goos.
goosvanbeek
 
Posts: 3
Joined: Wed Oct 02, 2013 4:38 pm

Re: Linked server connection error when using a domainaccoun

Postby Moderator » Thu Oct 10, 2013 12:52 am

Well, then "login mappings" can be next try. My recollection is that similar issue was happening and was resolved by setting "Local server login to remote server login mapping". I will need to search in archives if this is the case.

Another possible reason is access to a license file. Are you using trial, or commercial license?
User avatar
Moderator
Site Admin
 
Posts: 279
Joined: Wed Oct 29, 2008 11:27 pm

Re: Linked server connection error when using a domainaccoun

Postby goosvanbeek » Thu Oct 10, 2013 11:29 am

Thanks again for your reply.

Creating a login mapping didn't solve the problem either. Until I tried a different domain account. With that second account I could access the database.
I'll discuss this issue with our IT department so they can compare these two accounts and hopefully solve the problem.

Thanks again, I appreciate your support very much.

Best regards,
Goos van beek.
goosvanbeek
 
Posts: 3
Joined: Wed Oct 02, 2013 4:38 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 0 guests