July 22, 2016 at 7:22 am
Just getting this error when i try to create a Linked server from Server2 a SQLServer 2008 R2 server to another SERVER1, SQLServer 2008 R2 server.
SQL Server Network Interfaces: Connection string is not valid [87].
OLE DB provider "SQLNCLI10" for linked server "SERVER1\MSSQLSERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "SERVER1\MSSQLSERVER" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (.Net SqlClient Data Provider)
I have checked through:
On Server1 Firewall settings and Port and no firewall restriction is in place. port is default 1433. TCP is enabled. Named Pipes is disabled. In Server Configuration Manager the SQLServer Browser is stopped. The server has 1 Only instance and is the default.
Both servers are in the same Network domain
I used this setting:
Remote Login: Be made using this security context: supplied name.
Any pointers?
July 22, 2016 at 7:36 am
Start here:
Connection string is not valid
Have a look at the connection string. Does it look right? If it does, please post it here, changing any sensitive information.
John
July 22, 2016 at 8:00 am
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1\MSSQLSERVER', @srvproduct=N'SQL Server'
...
.....
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SERVER1\MSSQLSERVER', @locallogin = NULL , @useself = N'False', @rmtuser = N'admin.fraka', @rmtpassword = N'xxxxxxxx'
GO
is this helpful?
July 22, 2016 at 8:04 am
Sorry i pasted the wrong info earlier on. This is the right information used to create the Linked server that failed:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SERVER1\MSSQLSERVER', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVER1\MSSQLSERVER', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SERVER1\MSSQLSERVER', @locallogin = NULL , @useself = N'False', @rmtuser = N'admin.fraka', @rmtpassword = N'xxxxxxx'
July 22, 2016 at 8:13 am
Is the SQL Server Browser service running on the remote server? I'm not sure whether or not it tries to connect on 1433 for a named instance. If it's not running, try changing the @datasrc parameter to SERVER1,1433 when you create the linked server. Does the remote server capture failed logins in its errorlog? If so, do you see any when you attempt to connect over the linked server?
John
July 25, 2016 at 7:07 am
Thank you. The Browser service is running as started and on automatic on remote server called Server1. I have changed the name to just server1 but i get the below error a slightly shorter one though.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties.DoPreProcessExecution(RunType runType, ExecutionMode& executionResult)
===================================
Login failed for user 'xxxxxxxxx'. (.Net SqlClient Data Provider)
July 25, 2016 at 8:51 am
Is your SQL Authentication configured for mixed mode or Windows only?
July 26, 2016 at 1:54 am
It is in a mixed mode (SQLserver and Windows Authentication mode)
Thank you for having a look at this for me. The problem is still occuring.
July 26, 2016 at 2:24 am
And the account you are using, is it a SQL account or windows account?
Sorry for silly questions, I am just trying to rule out the obvious.
Also, can you connect to the other instance with thos credentials?
July 26, 2016 at 2:29 am
It's a SQL account. We can see that from the parameters for sp_addlinkedsrvlogin. What we still don't know is what message, if any, is appearing in the errorlog of the the remote server when the login fails.
John
July 26, 2016 at 2:44 am
Sorry and i thank you very much.
I was using windows account before and it failed to run under that context.
I have created a SQL server account and that works.
interestingly there is not a single error logged on the remote server about the errors. Nothing in SQL error logs or eventviewer.
If I were using windows account e.g. domain\username what is the right way of entering the account in the input box for the security context. Is it then domain\username or just username part of it. Anyway both did not work.
This is suppose to run in mixed mode. Even the 3rd option to run using the logged in user's account did not work either.
With the questions you asked i was able to get on the right track at the moment.
July 26, 2016 at 3:15 am
If you were using a windows account, you would specify a login credential, there are options to either use credentials from the login accessing the account, or you can specify to have the linked server service all requests via a different credential.
See below:
Your code for the linked server to use windows credentials from the requesting user:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST', @locallogin = NULL , @useself = N'True'
GO
If you wanted to service the linked server login with a different windows login:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST2', @locallogin = N'Domain\Username', @useself = N'True'
GO
Glad you're on the right track.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply