How to make a connection to a subscriber database ?

Question

How to make a connection to a subscriber database ?

bob mazzo

SSCommitted

Points: 1684
More actions
October 25, 2006 at 11:45 am

#170251

It may seem like a simple step, but as I setup a subscriber I simply cannot connect to another Sql 2005 DB. As I go thru the Subscriber wizard and choose a target server, I see the target server in the list. However, when I attempt to connect to the server it constantly fails.
I've tried the "sa" id and password. I've also tried to create a "repl_distribution" login on the target, yet nothing is working ?
Any advice would be greatly appreciated.
Sincerely,
Bob

Viewing 9 posts - 1 through 8 (of 8 total)

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

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 1

Have you checked that you can connect to the other server via SSMS? This may give you an indication of where to start looking for the actual problem.

Is there some kind of error message that gets returned about the failed connection?

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

debrucer1 SSCertifiable Points: 5366 More actions · Answer 2

Is the other DB (the new subscriber) set up in mixed mode? (i.e., to use mixed mode authentication).

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982

bob mazzo SSCommitted Points: 1684 More actions · Answer 3

SSMS, meaning the Sql Mgmt Studio ? Yes, I absolutely can connect to my subscriber machine. I can get to the Sql Studio via the "sa" id and create databases as I wish.

In fact I do have it setup in mixed mode. Also, since I already have Sql 2000 on my subscriber box I created an instance name of "Sql2005" during the 2005 install (although I can't imagine this would make any difference).

The message I'm getting is :

"Failed to connect to sql server DevServer\Sql2005 "

"An error has occurred...this failure may be caused by the fact that under default settings Sql server doesn't allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection) (Micro Sql Server, Error: 1326) "

Please help if you can.

Thanks,

Bob

bob mazzo SSCommitted Points: 1684 More actions · Answer 4

Sql 2005 Surface Configuration Tool !!! The remote connection was disabled in here !

Thanks to Rick Strohl's post at http://www.west-wind.com/WebLog/ShowPost.aspx?id=4493

Thanks again for the input, folks.

Bob

bob mazzo SSCommitted Points: 1684 More actions · Answer 5

Now that I can make the sql 2005 connection to my subscrip server, I have a new problem. It fails to create the subscription, claiming that the Win user id is invalid. Here's the error below (please note that I've created the windows login "repl_distribution"):

- Creating subscription for 'S1212002\SQL2005' (Error)

Messages

* SQL Server could not create a subscription for Subscriber 'S1212002\SQL2005'. (New Subscription Wizard)

------------------------------

Proxy "s1212002\repl_distribution" is not a valid Windows user.

Changed database context to 'orderfx1202'.

Job 'S1212020-orderfx1202-Orderfx1202_Order_Fx_-S1212002\SQL2005-20' started successfully.

Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account. (Microsoft SQL Server, Error: 14529)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=14529&LinkId=20476

bob mazzo SSCommitted Points: 1684 More actions · Answer 6

Somehow I just ended up solving the windows user problem. I added repl_distribution user to the Admin group in Windows (probably not a good idea).

BeerBeerBeer SSCarpal Tunnel Points: 4021 More actions · Answer 7

You probably only needed to add repl_distribution as a login to the SQL Server instance.

Using SSMS (yes, SQL Server Management Studio), just use the object explorer to drill down to Security\Logins and add the login giving them access to the necessary DB. Of course, then you need to ensure the correct permissions for them to do all they need to do.

What you have done is added this user to the BUILTIN\Administrators SQL Server group simply by adding it to the Windows Admin group. This gives the users rights to do just about anything within your SQL server instance.

Regards,

Steve

Life without beer is no life at all

All beer is good, some beers are just better than others

Dave Semmelink SSC Enthusiast Points: 117 More actions · Answer 8

Hi Steve, I hope you can help me too.

I am trying this out on a test system and am getting the same message:

Msg 14529, Level 16, State 1, Procedure sp_add_proxy, Line 45

Proxy "test-svr1\administrator" is not a valid Windows user.

I set up the publisher on test-svr1 and am trying to set up the subscriber on test-svr2. (I wouldn't use the administrator account on a live system but I'm just trying it out for now.) The servers are not part of a domain.

In Windows Explorer I can connect to test-svr1 from test-svr2 logging in as administrator with the password I supplied when setting up the subscriber.

Thanks for any assistance.