March 15, 2012 at 2:53 pm
Snapshot of a virtual server with SQL Server instance 2008 already installed is copied to alternate location and stood up. Want to replicate from original virtual server to copy of server. Publication is a snap to create. However, when setting up subscription, unable to connect. (two locations are connected by a VPN, and the servers are 'local' to each other through the VPN)
In order to distinguish between the two servers, renamed the copy by adding a prefix 'DR-'. When connected to 'DR-' instance, windows logins still reflect the original server's name in the security section under the 'DR-' instance. When logged onto the original server, by using the internal (local) IP address, can connect to the copied server using windows authentication in SSMS with no difficulties (table contents differ, which allows me to confirm which server is connected to).
BUT - replication does not allow the use of an IP address, but 'assumes' domain name style identification, where you must specify '<server name>\<instance name>'. When attempting to connect in this way, receive a 'Cannot connect to <server name>\<instance name>.' error: 'The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server).
Connectivity works between the two servers, because I can connect when specifying the IP address. (have reviewed PinalDave's and other articles regarding the 26 (and 40) error messages.
Is the problem with the login id? Or does the process even get that far? Shared memory, TCP/IP, and named pipes are all enabled at both ends, VIA is turned off. I do NOT know what IP address the local server has at the far end of the VPN tunnel, but I assume it's not the same as its local IP address (does that even matter?).
March 16, 2012 at 7:48 am
A few possibilities to look into on this:
1. Are your systems both in an Active Directory Domain? Can you ping each server from the other by name NOT IP? If not, it sounds like you might have a DNS issue.
2. You could try (although this is something of a "hack job" solution, and can break easily) adding an entry to the HOSTS file on both servers. This would "link" the IP of the server to its name.
As an example for #2:
On the "local" servers hosts file put in (the # indicates anything after is a comment)
192.168.23.100 SQLServer02 #This is the IP and name of the server at the remote location
The on the "remote" servers hosts file:
192.168.22.100 SQLServer01 #This is the IP and name of the server at the main location
Obviously, replace the names and IPs with whatever is appropriate for your environment. The problem with this solution, is that if the IP of either server changes, the whole thing breaks until you correct the hosts file. Mind you, without more details of your setup, this is all speculation...
Jason A.
March 16, 2012 at 8:45 am
When connected to the SQL Server on the copy, what does SELECT @@SERVERNAME return?
Check out this article, http://msdn.microsoft.com/en-us/library/ms143799.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 16, 2012 at 3:09 pm
@ Jason A:
1. There is no AD. Two separate 'work groups'
2. The host files have been edited.
3. Ping resolves the name to the correct IP address, going in both directions.
@ Jack
I was unable to force a change in the server name, following the directions on a different article (I forget which at the moment - this was work I did yesterday morning). That is to say, the results of select @@servername never changes. HOWEVER, after i execute sp_dropserver and sp_addserver, I have two entries when I execute sp_helpserver: both have the original database name and the current server name (with a DR prefix added). In other words, before attempting any changes, contents consisted of '<server name>\<database name>' and now consist of 'DR-<server name>\<database name>'.
Only the server name changes. This would be consistent with not being allowed to create a new instance of SQL Server without uninstalling and reinstalling, to change the name. Which is the direction I am now pointed to, since I don't seem to be able to control 'legacy' logins from the original virtual server.
Steve
March 17, 2012 at 8:27 am
steve smith-401573 (3/16/2012)
@ Jason A:1. There is no AD. Two separate 'work groups'
2. The host files have been edited.
3. Ping resolves the name to the correct IP address, going in both directions.
@ Jack
I was unable to force a change in the server name, following the directions on a different article (I forget which at the moment - this was work I did yesterday morning). That is to say, the results of select @@servername never changes. HOWEVER, after i execute sp_dropserver and sp_addserver, I have two entries when I execute sp_helpserver: both have the original database name and the current server name (with a DR prefix added). In other words, before attempting any changes, contents consisted of '<server name>\<database name>' and now consist of 'DR-<server name>\<database name>'.
Only the server name changes. This would be consistent with not being allowed to create a new instance of SQL Server without uninstalling and reinstalling, to change the name. Which is the direction I am now pointed to, since I don't seem to be able to control 'legacy' logins from the original virtual server.
Steve
Having just done this - that is, cloning a server with SQL Server installed I found there are a couple of things you need to do on the cloned system.
1) Drop/Add Server (sp_dropserver 'old server name'; sp_addserver 'new server name', local;)
2) Restart SQL Server - required to get new server name
3) Rename local groups to reflect new server name (not required, but done for consistency)
4) Add local groups to SQL Server and set permissions appropriately
5) Remove old local groups from SQL Server
6) Restart SQL Server
The local groups that are included in the cloned system actually point back to the original system. Without adding the local groups from the renamed server you don't get full security for the services.
When you add the new server in SQL Server (sp_addserver), you need to specify the local parameter and it requires a restart.
Since these servers are not part of a domain - the local users that you have created and added to SQL Server will need to be dropped and recreated. Once the logins are dropped and recreated - permissions should synch back up, but will still show in the database as the old login.
To make this a bit simpler, I would recommend that you create windows groups and add those groups to SQL Server instead of individual users. Then, all you need to do is drop the old servers group and add the new servers groups. Probably wouldn't hurt to build scripts that drop the logins and database users, and rebuilds them all on the DR server - would make sure all permissions are correct.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 17, 2012 at 10:38 am
Jeffrey,
Thank you for the list! Most interesting.
Question: What do you do when step 1 fails? (as it did, when I attempted to drop the servername)
Steve
March 17, 2012 at 11:07 am
did you cycle sql server?
Did you use the local parameter in the add?
Didn't fail for me on the 5 servers I just did this week.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 19, 2012 at 7:12 am
Jeffrey,
the 'drop' command fails.
Steve
March 21, 2012 at 4:42 pm
Ultimately, what I did was blow away the SQL Server instance and start over. Once I did that, I was then able to connect remotely using <server>\<instance> in SSMS, which I had NOT been able to do before. However, I am still unable to connect remotely for purposes of replication - still getting a 26 error. The replication question is being pursued in a different thread: http://www.sqlservercentral.com/Forums/FindPost1270479.aspx
Please close this thread and go to the other one, if you want to follow the events or contribute. Thank you!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply