Linked server connection error when using a domainaccount

More
6 years 7 months ago #10308 by goosvanbeek
goosvanbeek created the topic: Linked server connection error when using a domainaccount
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'

Please Log in or Create an account to join the conversation.

More
6 years 7 months ago #10310 by Moderator
Moderator replied the topic: Re: Linked server connection error when using a domainaccoun
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'

Please Log in or Create an account to join the conversation.

More
6 years 7 months ago #10312 by goosvanbeek
goosvanbeek replied the topic: Re: Linked server connection error when using a domainaccoun
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.

Please Log in or Create an account to join the conversation.

More
6 years 7 months ago #10313 by Moderator
Moderator replied the topic: Re: Linked server connection error when using a domainaccoun
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?

Please Log in or Create an account to join the conversation.

More
6 years 7 months ago #10314 by goosvanbeek
goosvanbeek replied the topic: Re: Linked server connection error when using a domainaccoun
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.

Please Log in or Create an account to join the conversation.

Time to create page: 0.083 seconds
Powered by Kunena Forum