Adding Linked Servers

Question

Adding Linked Servers

Viewing 4 posts - 16 through 18 (of 18 total)

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

TexasJohn Default port Points: 1476 More actions · Answer 1

Another question. should the SQL Server Browser service be running? Or is that to give clients access to SQL not the other way around?

John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

No, SQL Server Browser is just to resolve instance name to port name on the local computer.

John

TexasJohn Default port Points: 1476 More actions · Answer 3

Does anyone want to take a stab at why my SQL server to SQL server linked server is not working.

Here is what I have done and the results.

I am logged on to SQL Server 2014 and trying to set up a link to SQLEXPRESS 2012.

/* Add the BCM SQL database to a named linked server */

EXEC sp_addlinkedserver

@server=N'bcm'

, @srvproduct=N''

, @provider=N'SQLNCLI11'

, @datasrc=N'srv-apps01\SQLEXPRESS'

, @provstr=N'UID=Administrator;PWD=<password>'

, @Catalog=N'BrochsteinsMaster'

GO

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'bcm'

, @useself = 'False'

, @rmtuser = 'Administrator'

, @rmtpassword = '<password>'

GO

exec sp_tables_ex @table_server='bcm'

I get a return of table headings ... TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS

And the error message...

Msg 18456, Level 14, State 1, Line 53

Login failed for user 'Administrator'.

I do know the admin password.

But just in case the SQL Admin password is different I've also set up another user ID and set the password myself and still get the same results.

Also it makes no difference if I use SQL authentication or Windows

The providers I have listed on my server are ADsDSOObject, Microsoft.ACE.OLEDB.12.0. Microsoft.ACE.OLEDB.15.0, MSDAOSP, MSDASQL, MSOLAO,SQLNCLI11, SQLOLEDB.

John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.

TexasJohn Default port Points: 1476 More actions · Answer 4

I found the answer in the Event logs (the first place I should have looked).

Login failed for user 'Administrator'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.

John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.