Identity insert on linked server fails

You can insert an identity value into a table with an identity column on a linked server with the "SWITCH TO" trick.

If you haven't used the "SWITCH TO" trick to add and remove identity on a column, it's very quick, even on large tables!

Conceptually you simply create a new SCHEMA exactly like the table you are wanting to INSERT to without the identity defined. Then switch the table to that SCHEMA and do your INSERT. Then switch back to the SCHEMA with the identity defined.
The sample below has been tested on a linked server in AZURE. All the caveats of using "SWITCH TO" apply (indexes must be the same, drop and recreate foreign keys, etc)

To test, you can run the full script below on an Linked Azure SQL Server database. You'll need to do a find/replace with [LINKED_SERVER_NAME] and [DATABASE_NAME], replacing with your values. On a non-Azure DB you may need to add "ON PRIMARY" to the table creations.

--Let's setup the example by creating a table with an IDENTITY column on the Linked Server
EXEC('
CREATE TABLE [DATABASE_NAME].[dbo].[Example_Table](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](10) NULL
)
 '
) AT [LINKED_SERVER_NAME]

--INSERT some data into the table
INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] ([Name]) VALUES ('Travis')
INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] ([Name]) VALUES ('Mike')

-- Looks good
SELECT * FROM [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table]

GO


-- Create a TABLE with an identical schema, without the identity defined
EXEC('
CREATE TABLE [DATABASE_NAME].[dbo].[Example_Table_temp](
    [ID] [int] NOT NULL,
    [Name] [nchar](10) NULL
)
 '
) AT [LINKED_SERVER_NAME]

--Now Use the "SWITCH TO" to move the data to the new table
EXEC('
 ALTER TABLE [DATABASE_NAME].[dbo].[Example_Table] SWITCH TO [DATABASE_NAME].[dbo].[Example_Table_temp]
  '
) AT [LINKED_SERVER_NAME]


--Drop the old table (It should now be empty, but you may want to verify that if you are unsure here)
EXEC('
 DROP TABLE [DATABASE_NAME].[dbo].[Example_Table]
 '
) AT [LINKED_SERVER_NAME] 

--Rename the new table back to the old table name
-- NOTE the lack of database and owner identifiers in the new name
-- NOTE the use of double single qoutes (ESCAPED single quotes)
EXEC('USE [DATABASE_NAME];
  EXEC sp_rename ''[DATABASE_NAME].[dbo].Example_Table_temp'',''Example_Table''
   '
) AT [LINKED_SERVER_NAME] 
  

  -- Now do your IDENTITY INSERTs !!!!

 INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] (ID,[Name]) VALUES (888,'Travis')
 INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] (ID,[Name]) VALUES (999,'Mike')

 --Verify they got put in
SELECT * FROM [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] 

  
  --Now let's switch it back to our SCHEMA with an IDENTITY

  EXEC('
CREATE TABLE [DATABASE_NAME].[dbo].[Example_Table_temp](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](10) NULL
)

 ALTER TABLE [DATABASE_NAME].[dbo].[Example_Table] SWITCH TO [DATABASE_NAME].[dbo].[Example_Table_temp]

 DROP TABLE [DATABASE_NAME].[dbo].[Example_Table]

  EXEC sp_rename ''[DATABASE_NAME].[dbo].Example_Table_temp'',''Example_Table''
     '
) AT [LINKED_SERVER_NAME] 

--Data is still there
 SELECT * FROM [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] 
 GO
 -- And note you can no longer INSERT the IDENTITY
 INSERT INTO [LINKED_SERVER_NAME].[DATABASE_NAME].[dbo].[Example_Table] (ID,[Name]) VALUES (45,'Travis')
GO

IDENTITY_INSERT doesn't work with linked servers AFAIK, unless you execute dynamic SQL that includes the SET IDENTITY_INSERT in the batch or have some code (Stored Proc for instance) on the remote server which does that for you.

The IDENTITY_INSERT is per-session (see MSDN) and when you use the remote server this will probably be in a different session from your statement executed via [LINKEDSERVER].tempdb.sys.sp_sqlexec, which causes it to fail as you see it happening.