Cannot create a publication. Error 15404.

Question

Cannot create a publication. Error 15404.

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Rick Kelly Ten Centuries Points: 1279 More actions · Answer 1

We run replication as linked (or remote) servers with SQL Server running under a domain service account across all of our production sql servers. We have about 30. The linked server properties all use the logins current security context.

I think I am going to set up a couple of VMs without the other group security policy stuff we have on the prod servers and see if I have they same issues. I suspect we won't and I can pass this problem off to our network and security guys and tell them to fix it.

I'll post an update as to what happens. If I do solve this, I will post it here because, like I said, I have found other people having the same issue and they tried dozens of suggestions and none worked. I have yet to find a forum where someone came back and said "We fixed it, this is what we did..." I wonder if they all gave up, or decided not to bother posting once they solved the issue.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

alen teplitsky

SSC-Dedicated

Points: 30014

December 28, 2007 at 11:58 am

#763373

is there a kb article for this issue?

Rick Kelly Ten Centuries Points: 1279 More actions · Answer 3

Not that I could find. When you look up the error on MSs site it says there is no further info available.

Rick Kelly Ten Centuries Points: 1279 More actions · Answer 4

I did successfully setup replication on two other systems. 2005 distributor and a 2000 publisher. So it is something to do with the security setttings on the servers. I am sure the guys that set this up are missing something here that would allow SQL Server to still function properly. This is one of a few connection issues I am having since they implimented this stuff.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

if you are allowed, have you tried to alias the domain account you are running sql as to dbo on your servers? there is an sp for it, but i can't remember the name

Rick Kelly Ten Centuries Points: 1279 More actions · Answer 6

I was able to recreate the error on the test systems by adding a domain user as a sysadmin.

The error that I got on the other systems was for each domain user that was setup as a sysadmin.

That may be the issue that the MS guy was referring to. I wonder if as a work around if I temporaliy remove those accounts until I create the publication or add them to a group and add the group if that would be a functional workaround.

I'll give it a try.

Rick Kelly Ten Centuries Points: 1279 More actions · Answer 7

Noob,

If you get a chance, could you let me know if you have any domain accounts setup in SQL Server as sysadmins.

This seems to be where the problem comes in. As soon as I add one the publication fails to create because of the error that I listed above. If I delete the account I can create the publication without issue (from SSMS, SQL EM and from a query running the create publication SP.)

I noticed in the grant publication access SP where it looks for a list of users and is supposed to ignore as sysadmins, which it does when the distributor is a 2000 system. But it fails on that same list of sysadmins when the distributor is a 2005 system.

Thanks.

Rick Kelly Ten Centuries Points: 1279 More actions · Answer 8

I removed the individual users as sysadmins, created an AD group and added the AD group as a sysadmin and I was able to add a publication without the errors.

I need to investigate a bit more but it looks like the failure is caused when the sp_grantpublicationaccess SP goes through the list of sysadmins. It looks at the sysadmin column in syslogins where the value is 1, however it seems to ignore them (as it should anyway) if the isntgroup = 1. It only fails on the logins where sysadmin and isntuser both = 1.

So it looks like I can work around this issue by removing the sysadmins individual accounts and adding the back in an AD group.

I will give this a try in the prod environment on Monday and update.

Rick Kelly Ten Centuries Points: 1279 More actions · Answer 9

SUCCESS!!!

For anyone else that has this problem, the users that show up in the error are probably sysadmins. If you create an AD group and add the users to the AD group, you can then grant the AD group login permissions and add it to the sysadmin server role. Delete the individual logins and then try to create your publication again.

I did this today and it worked like a charm. Now let's see if I can create subscribers for the publication on the 2005 system.

Thanks for nothing Microsoft SQL Server Supprt!:D

Rick Kelly Ten Centuries Points: 1279 More actions · Answer 10

Everything thing else ran smooth as silk. Replication is now up and running and everything is running great.

The only weird thing that I ran into was having to run the sp_changedistpublisher SP to activate the publisher before the snapshot agent would run when I created a subscription. Not sure why, I never had that problem before in the 30+ times I have setup replication from 2000 to 2000, but that was a minor issue. (run on the publisher: sp_changedistpublisher 'publisher', 'active', 'true')

Anyway, I wanted to update this post in case someone else runs into the same issue and can't find anything else on the Internet like I did and has to call MS Support, whom in my case was no herp at all.

balu4k SSC Enthusiast Points: 106 More actions · Answer 11

I faced the same problem and found that the db owner doesn't exists in the domain.

so change the db owner to a vaild use in domain and should get solved. email me if you have any question balu4k@yahoo.com

USE dbname

GO

EXEC sp_changedbowner 'domain\account'

GO

pbowman-543344 Mr or Mrs. 500 Points: 556 More actions · Answer 12

On the

Msg 15404, Level 16, State 10, Procedure sp_grant_publication_access, Line 136

Could not obtain information about Windows NT group/user 'johndoe', error code 0xffff0002.

Error when trying to add a publication from a 2000 publisher via a 2005 distributor.

sp_addpublication calls

exec @retcode = dbo.sp_grant_publication_access

@publication = @publication,

@login = null,

@reserved = 'init'

sp_grant_publication_access declares a cursor for all sysadmins local to the Publisher

declare hC CURSOR LOCAL FAST_FORWARD for

select loginname from master..syslogins where

(is_srvrolemember('sysadmin', loginname) = 1 or

sid = suser_sid())

and then for each login calls

EXEC @retcode = @distproc

@publisher = @@SERVERNAME,

@publisher_db = @database,

@publication = @publication,

@login= @login2,

@operation = 'add',

@Skip = @Skip

Where @Skip is 1 & @distproc = RTRIM(distributorsrvname) + '.' + RTRIM(distribdb) + '.dbo.sp_MSpublication_access'

i.e. it’s an rpc call to sp_MSpublication_access (actually in sys schema in 2005). That in turn does..

select @sid = suser_sid(@login,0)

select @isntuser = null

select @isntuser = isntuser

from master.dbo.syslogins

where sid = @sid and hasaccess = 1

--

-- Avoid EXECUTE AS LOGIN failure and cover the case of that

-- the NT user is not in syslogins (unprovisioned login)

--

if @isntuser is null and @sid is not null

begin

exec @retcode = master.dbo.xp_logininfo

@login, N'all', @privilege output

if @privilege is not null

select @isntuser = 1

end

Now login “johndoe” that is mentioned in the error, is not a login on the distributor, HOWEVER

select @sid = suser_sid(@login,0)

Does not return NULL, as you would normally expect, it returns a sid. Hence xp_logininfo gets called for ‘johndoe’ and the error at the top is the result.

Why does suser_sid return a sid for a login not on the instance? Microsoft only knows I guess.:crazy:

WORKAROUND:

Basically get a list of all the sysadmin logins from your 2000 publisher that are not on your 2005 distributor, check each one with suser_sid. If any return a sid when they shouldn’t, then either temporarily remove sysadmin permission from that login on the publisher or, if that’s not possible, try adding the login to the distributor temporarily (haven’t tried this last one)

NB this is just what we found worked for us, no guarantees that it’s exactly the same issue as other people have had.

dmagers SSC Veteran Points: 224 More actions · Answer 13

Thanks to everyone for their input on this subject. I, like others that posted here, searched endlessly for a resolution to this error between a 2005 (2008 in my case) distributor and a 2000 publisher. I temporarily changed the user which was showing up in the error to not have sysadmin privledges and everything is working fine now.

yasirbam SSC Veteran Points: 208 More actions · Answer 14

Hi,

I have a similar problem, I have a distributor sql 2005 SP3 with a publisher sql 2000 SP4 build 2148 and this setup is working with no problems. (i.e. pull transactional replication).

now, i am trying to configure another publisher which is sql 2000 sp4 build 2151 to use the same distributor, but I am getting this error when trying to configure the publisher using SSMS and sql.

"Could not obtain information about Windows NT group/user 'username', error code 0xffff0002"

I have been looking at this issue for a couple of days now and i think this bug was introduced somewhere after the sp4 build number 2148 to 2151

hope that someone from Microsoft can confirm if this is the case or not.

Regards,

Yasir

yasirbam SSC Veteran Points: 208 More actions · Answer 15

By crearting the login on the distributor with sysadmin server role, i managed to configure the publication.

Regards,

Yasir