Invalid authorisation linked server

views:

answers:

+3 Q:

Invalid authorisation linked server

Want to copy data from 1 table to other and they are in different servers.

I have set 2 servers as linked servers. In SQL Server Management Studio, Server Objects --> Right-click Linked Servers, and then New linked server. I gave the connecting server as my servername SERVER2 and type as SQLSERVER.

Now i executed the query, Insert Into Server1.Database1.dbo..Table1 (Col1, Col2) Select Col1, Col2 From Server2.Database2..dbo.Table2

It is throwing an error message, The OLE DB provider "SQLNCLI" for linked server "SERVER2" has returned the message "Invalid authorization specification".

I have no idea where to change the authorisation. Please help me to solve me the same. It is throwing an error message, The OLE DB provider "SQLNCLI" for linked server "SERVER2" has returned the message "Invalid authorization specification".

I have no idea where to change the authorisation. Please help me to solve me the same.

+1 A:

In SSMS, Server Objects -> Linked Servers, Right-click on SERVER2 and choose Properties.

In the properties window, click on "Security" in the left hand panel.

In the section labeled, "For a login not defined in the list above, connections will:", choose the last option "Be made using this security context:". Then specify a login and password for an account on Server2 with appropriate permissions for the task you're trying to accomplish.

Joe Stefanelli 2010-06-23 15:27:47

Now the error is "An invalid schema or an invalid catalog was specified for the provider "SQLNCLI" for linked server "CTRZSQL.". still not able to copy the data..

satya 2010-06-24 06:17:10

If the syntax in your original post is accurate and not just a typo, then you have two periods between "Database2" and "DBO" where you should only have one: Server2.Database2.dbo.Table2

Joe Stefanelli 2010-06-24 13:20:00

I have modified that to Insert Into SERVER1.DB1.TBL1(F1)Select F1 From SERVER2.DB2.TBL2. Then it is throwing an error message Invalid object name SERVER2.DB2.TBL2.Something that is strange for me, when i queried this way it is copy data from other Server->Db->TBL.Insert Into TBL1(F1)Select * from openquery(LINKEDSEREVERNAME,'Select concession from DBNAME..TBLNAME'). Can you please explain me why?

satya 2010-06-24 13:31:08

You still need to fully qualify your tables with an appropriate 4-tuple in the form of: ServerName.DatabaseName.SchemaName.TableName

Joe Stefanelli 2010-06-24 13:38:23

Do you mean in the query or..? Can you please explain in detail, where i need to specify this 4-tuple?

satya 2010-06-24 14:05:10

I mean in the query. Your original posted syntax should work once you remove the double periods. Insert Into Server1.Database1.dbo.Table1 (Col1, Col2) Select Col1, Col2 From Server2.Database2.dbo.Table2

Joe Stefanelli 2010-06-24 14:16:30

Unfortunately that is not working for me..It is throwing me an error message:- Invalid object name SERVER2.DB2.TBL2. Do i need to modify any settings??

satya 2010-06-24 14:22:51

As I mentioned earlier, you need to fully qualify with a 4-tuple. Your example of SERVER2.DB2.TBL2 is only a 3-tuple which is missing the Schema name. Assuming the default DBO schema, you should be using SERVER2.DB2.DBO.TBL2.

Joe Stefanelli 2010-06-24 14:59:29

Thanks for ur patience. I think, Default schema used by SQL Server when objects are created by a database owner and a schema is not identified. Is there any other way to identify them?

satya 2010-06-24 15:09:36

DBO is the default schema. This query should help you identify the schema for a given table: select s.name as SchemaName from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id where t.name = 'YourTableName'

Joe Stefanelli 2010-06-24 15:17:23

Wow, it's working like charm. Thanku very much.

satya 2010-06-25 05:35:52

ansaurus

tags:

views:

answers:

Invalid authorisation linked server

related questions