The joys of authentication
-
I have mixed mode replication. I am trying to use a Login ID valid on a different system from the one I am using. The login id has administrator rights on the remote machine, and I am using a large-pipe LAN (1 GB internal network) so latency better not be an issue at the login stage.
If I use TCP/IP rather than named pipes (n.p. is NOT turned on), and am specifying both the login ID and password correctly, what can cause me to receive a 'login failed' error? Do I also have to assign rights within the database itself? Do I have to use the identical Login-ID on both machines for this to work correctly?
-
What is the errorr your getting?
Your login needs to have rights to some database to connect.
-
Y, if the login isn't a local admin and you don't have builtin\admins specified in SQL, then you won't be able to connect. Just being a windows acct, or having windows rights on that box won't get you into SQL.
What's your specific message?
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
In my event viewer on my workstation (event viewer on target server shows nothing), I receive the following message:
Replication-Replication Snapshot Subsystem: agent DELL4\BUYSPEED-FLETC-FirstTestForMerge-3 failed. The replication agent had encountered an exception.
Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ConnectionFailureException
Exception Message: Login failed for user 'SQLSecondPublisher'.
Message Code: 18456
Only windows level privileges have been assigned to the user id 'SQLSecondPublisher'.
IF SQL Server level privileges are required, then I'm suffering navigation issues in BOL (secondary to my challenges in solving this connectivity issue: I'm not finding where they are defined in BOL under replication. Is this me being a newbie or is this a valid criticism of BOL missing an xref?)
-
I'm not entirely sure what you just asked me, but bol can be a little thick and what you want isn't always outlined exactly where you'd expect to find it.
However, windows perms alone won't get you what you need. If you just want to see it up and running, then give the acct sql sa rights by creating a login that points to that acct and putting it in the sysadmin group.
If you want to refine the perms, then that really depends on what you're doing.
If there's something more specific I can help you accomplish, let me know.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
OK, I went into the target server and granted syadmin privileges to the Windows login within SQL Server. Doing so made no difference in outcome - I still get a login failed message.
-
OK, what's the exact error?
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
When I drilled down into the details of the log, and examined the SUBSEQUENT step, not the previous step as suggested in the log itself, I find this line:
2008-05-19 20:33:43.85 Message Code: 18456
Searching that message code leads me to this link:
http://support.microsoft.com/kb/889646
which tells me to add database creator and security administrator roles to the subscriber login-id.
So, I do that, and the error repeats itself.
-
First, you shouldn't grant sysadmin privleges. That's a huge security hole and unnecessary.
When replication runs, the account on the distributor logs into SQL Server just like any other user. So this account needs a login on the subscriber as well as the rights to create tables and apply data changes.
Are you on a domain here? I want to be sure that the account used in replication has rights across machines. Also, who setup replication? Have you run back through SSMS to edit replication and be sure that this is a Windows account and not a SQL account? they can be named the same thing.
-
Steve Jones - Editor (5/20/2008)
First, you shouldn't grant sysadmin privleges. That's a huge security hole and unnecessary.When replication runs, the account on the distributor logs into SQL Server just like any other user. So this account needs a login on the subscriber as well as the rights to create tables and apply data changes.
Are you on a domain here? I want to be sure that the account used in replication has rights across machines. Also, who setup replication? Have you run back through SSMS to edit replication and be sure that this is a Windows account and not a SQL account? they can be named the same thing.
I agree that sysadmin should not be granted. I was under the (mistaken?) impression that sysadmin implied all other rights, so I was trying to ensure that rights were not an issue. For the moment, every security option is turned on for this user id. The ID is a windows ID, and I am the sole author of what is going on - these are not Domain-based systems, which is one reason that named pipes are turned off.
The overall objective is, with only two systems, set up a merge replication model so we can observe how the replication actually performs (under controlled environment).
I created two separate Windows Login IDs - one for the publisher and one for the subscriber. Using the wizard, I then initiated a merge replication, which fails when attempting to connect to the subscriber to so the initial transfer/copy of the database. I am having difficulty authenticating the subscriber's login ID. Since they were created last Friday (by me), and I controlled the password settings, I created them as batch-type IDs (user cannot change password, password does not expire).
When you say 'the account on the distributor' - are you saying that the Login-ID has to be identical for both the distributor/publisher AND subscriber? The wizard prompted me for providing the login ID and password for the subscriber, which led me to believe they did not need to be the same (why permit a violation of the process in a wizard?).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply