December 19, 2007 at 9:06 pm
I have had replication running in our environment for a couple of years on about 10 of 30 SQL Servers we have running 2000 EE.
Now I have to replicate to a 2005 EE 64 bit system from a 32 bit 2000 EE system.
Everything went great until I went to create the publication. It goes through everything but then I get the following error:
SQL Server Enterprise Manager could not create publication '[name]' from database '[name]' Error 15404: could not obtain information about Windows NT group/user 'Domain\User', error code 0x5.
I have a 2000 publisher and a 2005 distributor that will push to a subscriber on the 2005 system.
I have setup replication at least 25 different times and have had it running for 3 years and have never had an issue like this untill now.
Any ideas?
Also the user it returns the error for is not even a user in that database and is not orphaned in that database. The AD user name it is reffering to is my admin account that is a sysadmin on the server. SQL Server is in mixed mode on this system.
December 20, 2007 at 7:36 am
I am sorry, instead of replying I need your help.
You have written "I have a 2000 publisher and a 2005 distributor that will push to a subscriber on the 2005 system."
Please let me know the steps how you have created the replication, I tried but failed.
December 20, 2007 at 7:58 am
The first thing you need to do is setup your distributor. Then define the possible publishers before you create your publications.
What version are you using and at what point did it fail? What errors did you get?
December 21, 2007 at 6:10 am
I did the following steps as per the URL http://msdn2.microsoft.com/en-us/library/ms143241.aspx:
1. Configured Dev1 (SQL Server 2005) as the distributor.
2. Configured Dev2 (SQL Server 2000) as the publisher.
3. Created a merge publication (test123 in test db) in Dev2 and got the following message
SQL Server Enterprise Manager successfully created publication 'test123' from database 'test'.
SQL Server Enterprise Manager could not start the Snapshot Agent because it could not connect to server 'DEV1'.
Note: I can register the DEV2 server in Management Studio with ‘sa’ login. But if I try to create the publication using Management Studio, I am getting the following error:
Creating Publication
- Creating Publication 'test456' (Error)
Messages
SQL Server could not create publication 'test456'. (New Publication Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
A severe error occurred on the current command. The results, if any, should be discarded.
Changed database context to 'test'. (Microsoft SQL Server, Error: 0)
4. Then I checked the jobs in DEV1 and found a job named 'DEV2-test-test123-3' and then I ran this snapshot job sucessfully.
5. Now when I tried to execute the stored procedure sp_helppublication_snapshot which returns information on the Snapshot agent for a given publication. This stored procedure is executed at the Publisher on the publication database. And got the following message:
"Msg 14013, Level 16, State 1, Line 1
This database is not enabled for publication."
Note: I executed the stored procedure as followed:
USE [test]
EXEC sp_helpmergepublication test123
EXEC sp_helppublication test123
EXEC sp_helppublication_snapshot test123
GO
Also I created another merge publication (test234 in test2 db) in DEV1 and ran the above script it is giving correct result.
6. I tried the step 5, because in the above URL it is written:
Using a SQL Server 2005 Distributor with a Publisher Running SQL Server 2000
SQL Server 2005 can be used as a remote Distributor for Publishers running SQL Server 2000. To change agent properties in this scenario, execute the following stored procedures at the Distributor. These procedures allow you to change properties that are new in SQL Server 2005:
sp_MSchange_snapshot_agent_properties (Transact-SQL)
sp_MSchange_logreader_agent_properties (Transact-SQL)
sp_MSchange_distribution_agent_properties (Transact-SQL)
sp_MSchange_merge_agent_properties (Transact-SQL)
If you have a Publisher and Distributor running SQL Server 2000, it is possible to change the credentials under which agents make connections using sp_changedistpublisher and sp_changesubscriber. However, if you upgrade the Distributor to SQL Server 2005, these procedures cannot be used to change credentials used in existing agent jobs (the procedures do affect agent jobs that are created after the procedure is called). In order to change the credentials for existing agent jobs, call one of the four procedures listed above.
Now please let me know how to configure this merge replication.
December 21, 2007 at 10:24 am
Are both of your SQL Servers running under the same AD service account?
December 21, 2007 at 2:58 pm
you need to run some update scripts to do this:
Using a SQL Server 2005 Distributor with a Publisher Running SQL Server 2000
SQL Server 2005 can be used as a remote Distributor for Publishers running SQL Server 2000. To change agent properties in this scenario, execute the following stored procedures at the Distributor. These procedures allow you to change properties that are new in SQL Server 2005:
sp_MSchange_snapshot_agent_properties (Transact-SQL)
sp_MSchange_logreader_agent_properties (Transact-SQL)
sp_MSchange_distribution_agent_properties (Transact-SQL)
sp_MSchange_merge_agent_properties (Transact-SQL)
see: http://msdn2.microsoft.com/en-us/library/ms143241.aspx
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
December 21, 2007 at 4:55 pm
This looks like it only works if you upgraded an already existing replication setup. I have a brand new system runing 2005 ad I cannot get to the point where the publication even gets setup. So, there are no agents to change or update.
Is that correct?
My problem comes from adding the publication. When I run the sp_addpublication stored procedure I get this error:
Msg 15404, Level 16, State 19, Procedure sp_grant_publication_access, Line 136
Could not obtain information about Windows NT group/user 'DOMAIN\USER', error code 0x5.
It returns this error for every login in the sysadmin role on the publisher. It does not happen if I create it with a 2000 distributor.
December 27, 2007 at 11:52 am
Just an update on this issue for anyone else that may be having the same issue.
After spending several hours on the phone with MS support, it appears that Microsoft broke the ability to use a 2005 distributor and a 2000 publisher in SQL Server 2005 Service Pack 2. This issue, according to them, has no workaround and is not expected to be fixed until Service Pack 3.:angry:
FANTASTIC!!!
I'll update this again if they ever fix it.
December 28, 2007 at 8:42 am
strange, we do replication from SQL 2005 SP 2 to SQL 2000 and no problems. even reinitialized 2 subscriptions last night to some ancient sql 2000 servers we have. even set up some new publications last month from SQL 2005 SP2 on 64 bit to sql 2000. distributor is build 3186 and the publishers are a mix of sql 2005 SP2, sql 2000 and same thing for the subscribers
are you using SSMS to connect to all servers? last year when playing with it i noticed it doesn't like it if you don't use SSMS for everything
i do the following
use SSMS to connect to publisher to create publication
use SSMS to connect to publisher and create a subscription. it will then go to subscriber and create it there
run snapshot
run logreader
and then run the subscription to initialize it
my SSMS is also patched with the latest hotfix from MS for SQL 2005 and uninstall SQL 2000 EM and stop using them to save you the headache. just use SSMS for everything
December 28, 2007 at 9:22 am
Are you running snapshot or transactional replication. I have tried everything to get this going including working with MS Support.
I am working with Management Studio. I tried just running the create publication stored proc on the publisher and got the same error.
Publisher is 2000 build 2040 and the distributor is 2005 service pack 2.
we also have to follow NIST requirements and use strong encryption when authenticating. NTLMv2(Kerberos). MS Support told me this is a known issue that they won't fix until service pack 3.
Odd, I wonder if this guy just did not know what he was talking about. If I use a 2000 distributor, everything works fine.
December 28, 2007 at 9:36 am
transactional here and i always use the GUI to create publications and subscriptions
only time i used sp's was when we went to a new distributor a few months back and scripted out all the publications from 5 different publishers and 2 old distributors. no way i was going to use the GUI for publications with 40 tables in a db of 300 tables or more
December 28, 2007 at 9:45 am
Are you running 2005 distributor with a 2000 publisher and 2005 subscripber at all? This is what I am trying to do.
The MS Support guy shared my system and we tried the GUI and the SPs. He then tried the same setup on his test servers and could not get it to work (so he says).
We have been running replication from 2000 to 2000 for years. This was the first attempt at 2000(pub) to 2005(dist) to 2005(sub).
If this is working for you, I wonder if this is only because of the security restictions we have. I have seen this error on other forums, but they are never resolved. Or at least they never posted how they finally fixed this.
Thanks for the info.
December 28, 2007 at 10:46 am
we used to have 2 SQL 2005 SP2 distributors and some time ago migrated to one 64 bit distributor that handles SQL 2005 SP2 and SQL 2000 publications. all transactional.
i think it was 2 months ago that i finally transfered one of them with a sql 2000 publisher to the new distributor that has a variety of subscribers. sql 2005 32 bit, 64 bit and sql 2000. we also have a bunch ancient servers that still say compaq at remote offices running sql 2000 and we replicate there over the same distributor from sql 2005 SP2 64 bit subscribers
if you have kerberos issues, have you tried to use the sa account for replication? my boss sets everything up with sa and i'm too lazy and like to click next without typing and end up setting up windows authentication and it works both ways for us
December 28, 2007 at 11:19 am
Thanks for the info.
I need to investigate this a bit more. I'm not sure the MS Support guy did not just give up and make up some BS excuse because he could not figure out what was wrong.
We can't run mixed mode here because we deal with sensitive government data and have a highly locked down network and systems. That alone has caused a bunch of issues with other SQL related stuff. I still think that is at the root of this problem.
December 28, 2007 at 11:24 am
not the first time for MS
we had an issue where one replicated table would replicate a lot slower than all others. MS tried to tell me it was a hardware issue even though other tables were more active. i think the fix was to recreate the publication.
how do you run replication? linked servers or MS DTC? our idiot sarbox auditors told us to force everything through DTC and right away replication broke. we didn't even bother to change DTC to run under the domain admin account from local system, we just changed it back to the way it was
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply