April 1, 2014 at 6:47 am
Good Morning Everyone,
I normally don't post any topics because I usually find a resolution by searching.
I'm trying to link our Prod Server to Dev Server, but am getting the error "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."
Both are not named instances, so I'm just using the server name.
For security, I am using a service account to connect to Prod Server
I can link the Dev Server on Prod, but I cannot link the Prod Server on Dev.
Any help would be great! Thanks
April 1, 2014 at 7:09 am
Does the server allow remote connections?
SELECT *
FROM sys.configurations
WHERE NAME = 'remote access'
Is the value field set to 0 or 1?
April 1, 2014 at 7:22 am
I should have mentioned that as well. Yes value = 1
April 1, 2014 at 7:33 am
If the server you're trying to connect to doesn't listen on port 1433, you'll either need to specify it in your connection string or create an alias on the computer you're connecting from.
John
April 1, 2014 at 7:34 am
Log onto the DEV Server. Try to ping the Production server. Do you get a response?
April 1, 2014 at 7:34 am
Sounds like a permissions issue between the service account from DEV to PROD - are they the same?
Can you post your scripts for both the DEV and PROD server?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 1, 2014 at 7:50 am
SQL is listening on port 1433.
Can't log into the actual server.
What scripts are you referring too? I was using the GUI to create the linked server. Service Account is the same on both servers.
Keep'em coming. Thanks again for the help. This is stalling my progress.
April 1, 2014 at 7:56 am
Wait, so you can't log into the server too? Did you try to ping it from the dev server?
Did you make sure to include the domain. So instead of say logging into sqlserver01 you logged into sqlserver01.domain?
Also, the service account being the same on both servers isn't good unless you have different passwords.
April 1, 2014 at 8:07 am
Sorry, thought you meant log into actual windows server. I can connect to Prod Server via SSMS.
Pinged Prod Server from Dev Server and no lost in packets.
Tryed adding domain and same error. Ex. servername.domain
Passwords are different. Permissions is the same.
April 1, 2014 at 8:18 am
Try logging into the production sql server from the dev box using that service account.
April 1, 2014 at 8:24 am
Let's rule out a security/permissions issue. On the DEV box, create the linked server using the script below, specifying either domain account that has appropriate access on both servers, or the sa account (this is only a test).USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'PRODSERVERNAME', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PRODSERVERNAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='YOURPASSWORD'
GO
EXEC master.dbo.sp_serveroption @server=N'PRODSERVERNAME', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PRODSERVERNAME', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PRODSERVERNAME', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PRODSERVERNAME', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PRODSERVERNAME', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PRODSERVERNAME', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PRODSERVERNAME', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PRODSERVERNAME', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 1, 2014 at 10:59 am
Got a response from IT and they said that the firewall won't allow a connection from any development box to any production box.
Thanks everyone for the help
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply