SSMS LinkedServer doesn't work with ODBC and Firebird 4.0/5.0

[luka4684]

I have installed ODBC 3.0 and create ODBC for my Firebird 5.0.

Then in SSMS I want to create Linked Server but it just stuck/just loading

What should I do to solve this issue, because with Firebid 2.5 and older ODBC it works normally

[irodushka]

Hi @luka4684

You have nothing to do but to wait while I investigate to find and fix this issue. Sorry for the inconvenience.
I'll come back with a solution as soon as possible.
Regards

[irodushka]

Hi @luka4684

I tried with the last build (3.0.0.15 from https://github.com/FirebirdSQL/firebird-odbc-driver/wiki) but didn't see any issues...
That's how it looks from my side:

Can I ask you to:

1. Try 3.0.0.15 build, it has some enhancements, maybe it will solve your problems (not very likely)
2. Give me please more details. A scripted FB database that you're linking to MSSQL may help very likely. + MSSQL version, Windows version, etc etc.

Regards

[hsl4711]

Hep

I am running with 3.0.0.16 build. Created a System DSN with the new ODBC driver.
Running SQL 2022 Developer latest patch. My linked server is looking like this.
Tried with and without remote user and password.
But keep getting Authorization failed - and I know this sounds so simple,
but I must have overlooked something. And the user/pass I have tried
works on FB. And it is Firebird 5.0

USE [master]
GO

/****** Object: LinkedServer [HOUSE] Script Date: 18/12/2024 10.16.48 *****/
EXEC master.dbo.sp_addlinkedserver @server = N'HOUSE', @srvproduct=N'FB', @Provider=N'MSDASQL', @datasrc=N'House'
/ For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HOUSE',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'HOUSE', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

[luka4684]

Hi @irodushka ,

I have tried but without success.
MSSQL version: 16.0.1135.2
OS: Microsoft Windows Server 2016 Standard

Firebird 5.0 configuration: firebird.txt

Database script:
-- Create a new database
CREATE DATABASE 'C:\Users\Luka\Desktop\simpledb.fdb'
USER 'sysdba' PASSWORD 'masterkey';

-- Connect to the newly created database
CONNECT 'C:\path\to\database\simpledb.fdb'
USER 'sysdba' PASSWORD 'masterkey';

-- Create Customers table
CREATE TABLE Customers (
CustomerID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(100),
DateAdded TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Orders table
CREATE TABLE Orders (
OrderID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
CustomerID INTEGER NOT NULL,
OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
TotalAmount DECIMAL(10, 2),
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
);

-- Insert sample data into Customers table
INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('John', 'Doe', '[email protected]');

INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('Jane', 'Smith', '[email protected]');

-- Insert sample data into Orders table
INSERT INTO Orders (CustomerID, TotalAmount)
VALUES (1, 99.99);

INSERT INTO Orders (CustomerID, TotalAmount)
VALUES (2, 149.49);

-- Query the data to verify
SELECT * FROM Customers;
SELECT * FROM Orders;

-- Commit the changes
COMMIT;

-- Exit the script
EXIT;

Linked Server script:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'FIREBIRD', @srvproduct=N'', @Provider=N'MSDASQL', @datasrc=N'LINKEDSERVER'

GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'FIREBIRD', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'FIREBIRD', @locallogin = NULL , @useself = N'False', @rmtuser = N'SYSDBA', @rmtpassword = N'masterkey'
GO

[irodushka]

Hi @luka4684 @hsl4711

Are you running MSSQL Management Studio as Administrator?

I wonder I have no such a troubles as you describe in my setup (Windows 10, MSSQL Express 15.0.2000, MSSQL MS 20.2) - except that I have FB4.0 server (not 5.0), but I'll be very much surprised if it's a matter of FB server version.

@luka4684 I've ran all your scripts, created DSN and linked server, and it works ok...

USE [master]
GO

/****** Object:  LinkedServer [TEST_FB]    Script Date: 30.12.2024 16:30:55 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'TEST_FB', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'TEST_DB_DSB'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST_FB',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'TEST_FB', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

[hsl4711]

Hep
Well it must be christmas or something else.
Works now with SSMS started as Admin or SSMS started as non-admin.
Only difference is, that I had 3 versions of Firebird SQL running (3, 4 and 5)
Yeah shouldn't have done that but there it is.
Removed 3 and 4 and voila! Works like a charm

[irodushka]

@hsl4711
Jesus saves all and everyone. Even SSMS & Firebird.