we have an outside company that replicates to us and we are known to them via an IP address. We get an error now...Sql Server 2005 now does not support a server Alias, IP address, or any other alternate name are not supports. Specify the actual Server Name
I tried putting in the hosts file on their server
168.168.110.2 SQL2005
able to connect to our server through Studio Manager...no problem....
replication....no dice
what is the work around to make this happen, I am not a network guru...know enough to kill someone, but I need to get this to work before I can move to the new production server
Thanks
John
Exactly what part is failing? Have you tried creating an alias and mapping it to the IP address?|||
When the outside company tries to set the Subscription up it fails since they try to enter the IP address. From what the network people tell me, we have a T1 line between us and open addresses so they can ping the IP address. But, we are on two completely different networks....2 different companies.
They tried to put it into the hosts file on their server (which is SQL 2000) and on the PC they were using studio manager on, but that did not help
When you say create an alias, what exactly are you referring to, since my network skills are enough to kill someone right now.
On their side, any network operations will take some paper work so we are hoping to keep it on the server....paper work just takes a bit too long sometimes
|||
the error we get
SQL Server Replication requires the actual server name to make a connection to the server.
Connects through a server alias, IP addres, or any other alternate name are not supported.
Specify the actual server name 'SQL2005'
Hi,
When you setup subscription, do you specify the subscriber using IP address or server name? I saw you have do the mapping with host file. You may want to check the following:
1) Make sure the outside company can connect to your server using name "SQL2005" using osql or management studio.
2) after connect to SQL2005, try "select @.@.SERVERNAME". Make sure the server name is "SQL2005" if it is default instance, or "SQL2005\instancename" if it is named instance
3) when create subscription, enter "SQL2005" instead of IP address.
Let me know if it still doesn't work for you.
Thanks,
-Peng
|||It seems we have gotten past one problem and on to a new problem.
It looks like the Alias took care of the problem.
now, when the wizard gets to "New subscription Wizard"
Choose one or more Subscribers and specify each subscrition database
We can choose "Subscriber" as "SQL2005" - which is the server
Great!
but, there are no "Subscription Database" to choose from
The sql user has access to the DB, they have "Connect Replication" and "Connect" permission. The sql user also has public permission to the DB.
What else could be missing?
I feel we are close, just trying to finish this
One other thing, I have read that SQL 2000 distributor will work with a sql 2005 Subscription if it is transactional, is that correct?
from another post "If you are using a transactional read-only subscriber then the subscriber can be up to two versions higher than the publisher. SQL 2000 publisher and SQL 2005 subscriber should work, "
|||Isn't "SQL2005" the publisher machine? The "Subscriber" should be the machine on the other end, the one receiving the data. You may need to click on the "Add Subscriber" button below and add the machine as a valid subscriber.
|||SQL2000 at the outside company is the publisher and distributor
they also set the subscription for us (Push).
I can not access there SQL Server at all since it has windows auth
We are SQL Server 2005 (not yet, maybe this weekend)
So they are pushing the data from their DB to us
SQL2000 -> SQL2005
which, according to the docs does work. There just seems to be some bloody security issue going on....seems Microsoft went overboard on some of this and now has made it a real pain in the neck to even get working.
What do they permissions have to be to get this working
|||Is "SQL2005" the actual name of your machine?
|||SQL2005 is the name of my server which is also a SQL2005 server
Windows 2003 Server - 64 bit, SQL Server 2005 Enterprise Edition 64 bit
The server will be renamed to what the production server is currently this weekend...right now we are just trying to test this replication issue.
We had the replication working a few minutes before we got a
the user is not associated with a trusted SQL Server Connection
Source:SQL2005
The user they are using on my server (SQL2005) now has every role and has every permission on the DB they are trying to replicate to.
|||So you're saying you got past the problem with adding a subscription?
And now you're having connection problems? Can you describe what exactly was working, and what failed suddenly?
And I assume this is transactional replication?
|||This is transactional replication.
First, we had the issues with trying to replication from 2000-> 2005 because of the IP address issue, that was fixed with using an Alias.
which the error we got clearly stated that a server alias could not be used....so I guess it can ....which the error screen sure does say that does not work. Of course, no help button on that screen to help a person out. That was one of the bigger huddles to get over.
the SQL User, since we allow both connections, has access to the DB and we gave them the ability to do "Connection Replication"
That does not seem to be enough.
The person on the SQL2000 server (who is using Studio manger by the way) was using the Subscription Wizard and could not even get any DBs in the drop down to select from
Now, I have given all permissions for the user for the DB and all roles for the entire Server.
He can now see the DB to replicate too on the SQL2000 Subscription Wizard and is able to select the DB.
So, we crossed through some security issue to allow that, but we have no clue which one.
This use to be a basic thing to really do, now it is complicated with too much security.
We are right now waiting to see if the subscription actually takes place......and waiting and waiting....atleast no errors yet.
So, in a nutshell
do a transactional replication from SQL2000 -> SQL2005
where the sql2005 can not do any of the setup because of security
the SQL2000 server has to be accessed with Studio manager - check
an Alias must be set up...even though the subscription wizard says that this is wrong
and then there is some permission besides "Connection replication" that has to be set that we can't figure out
|||
Couple things:
- To create a subscription and see the list of databases at the subscriber, you need to be a minimum of db_owner of the subscriber database, or be a member of the sysadmin role at the subscriber machine. This is documented in the "Security Role Requirements for Replication Setup" topic in SQL Server 2005 Books Online.
Since they connect to your subscriber machine using a standard login/password, that login needs to be at least a dbo of the subscriber database.
- Where do you see the "Connection replication" permission thing? If I remember correctly, you need to set permissions for the agents to connect to the publisher, distributor and subscriber machine. Did you start the snapshot agent, logreader agent and distribution agent yet? Do you see any failures?
|||They are failing on the Distribution Agent...snapshot and logreader does work
Login failed for user "The User is not associated with a Trusted SQL Server Connection. The Process Could not connect to Subscrivber "SQL2005" The Step Failed
The outside company can use SQL Server Man Studio to connect to our server using the same account, so we know the account works.....
something else is messed up still.
"Connection replication" is a permission of the user on the DB that we are trying to replicate to .....seemed like something we needed.
But right now, the user has every permission there is
|||By default, the subscription will be created and use windows authentication for all connections to the publisher, distributor and subscriber.
To change the security settings for the subscriber to use standard security, you need to modify the security settings for the distribution agent.
TO learn how to do this, see SQL 2005 Books Online topic "How to: View and Modify Replication Security Settings".
You can also access search for, or access this topic at msdn.com.
http://msdn2.microsoft.com/ms151761.aspx
No comments:
Post a Comment