FreeTDS Connection to MS SQL 2008

Forum related to MS SQL Server

Moderators: gto, cipto_kh, EgonHugeist

BastiFantasti
Fresh Boarder
Fresh Boarder
Posts: 8
Joined: 13.07.2015, 18:58

FreeTDS Connection to MS SQL 2008

Postby BastiFantasti » 23.02.2016, 13:27

Hi @all,

I'm trying to establish a connection to a MS SQL Server 2008 Express using the Freetds driver in zeos.

But so far without any success :( .
I've googled around, but did not find any configuration which was working. Or even any code snippet which would lead me into the correct direction.
Now I'm wondering, if I insert the correct values into the TZConnection object.

Here's a code snippet of what I did:

Code: Select all

var
  zCon : TZConnection;
 
begin
  zCon.Protocol := 'FreeTDS_MsSQL>=2005';
  zCon.LibraryLocation := GetCurrentDir + '\database\mssql\sybdb.dll';
  zCon.Database := 'testdb';
  zCon.HostName := 'win7vm'; //Details see below
  zCon.Port := 1433; //Tried with configuring port and without (default 1433)
  zcon.User := 'dummyuser';
  zCon.Password := 'dummypassword';
  zCon.Properties.Add('secure=yes'); //optional
  zCon.Properties.Add('trusted=yes');//optional
  zCon.Properties.Add('timeout=10');//optional
  try
    zCon.Connect; //<= Error or endless timeout comes up here
    zCon.Disconnect;
  except
    on e : Exception do ShowMessage('Error Class: '+e.ClassName + ' Error Message:' +e.Message);
  end;
end;


I've also configured the SQL server:
- added local sql user (not NT Auth)
- allow remote connections
- TCP/IP protocol enabled
- Firewall ports opened
- Databasenames as quoted strings (tried with and without)


Today I've switched zeos to the testing-7.3 repo and replaced the old ms*.dlls with the single sybdb.dll from the zeos svn repo

I played around with the server names like:
- serverip
- serverip\SQLEXPRESS
- serverip\MSSQL$SQLEXPRESS
- servername
- servername\SQLEXPRESS
- servername\MSSQL$SQLEXPRESS

I tested the optional properties secure and/or trusted


Connections to SQLite, Firebird 2.5, MySQL, PostgreSQL are all ok. Only MSSQL refuses the connection.


Any help is really appreciated

Best regards,
Bastian

miab3
Zeos Test Team
Zeos Test Team
Posts: 929
Joined: 11.05.2012, 12:32
Location: Poland

Re: FreeTDS Connection to MS SQL 2008

Postby miab3 » 24.02.2016, 18:27

@BastiFantasti,

Sample configuration that works for me:

Code: Select all

  object ZConnection1: TZConnection
    ControlsCodePage = cCP_UTF16
    ClientCodepage = 'UTF8'
    Properties.Strings = (
      'codepage=UTF8'
      'PreprepareSQL=ON')
    DesignConnection = True
    HostName = 'M64-PC\SQLEXPRESS'
    Port = 0
    Database = 'master'
    User = 'sa'
    Password = '****'
    Protocol = 'FreeTDS_MsSQL>=2005'
  end


Windows 8.1-64; Delphi XE2-32; ZEOS 7.2.1-rc; sybdb.dll in SysWOW64.

Did you read the comments included in the download?

Michal

BastiFantasti
Fresh Boarder
Fresh Boarder
Posts: 8
Joined: 13.07.2015, 18:58

Re: FreeTDS Connection to MS SQL 2008

Postby BastiFantasti » 01.03.2016, 12:36

Hi and thanks for your quick reply.
I will try your settings as soon as possible and come back with the results.

Best regards,
Bastian

BastiFantasti
Fresh Boarder
Fresh Boarder
Posts: 8
Joined: 13.07.2015, 18:58

Re: FreeTDS Connection to MS SQL 2008

Postby BastiFantasti » 03.03.2016, 08:46

Hi and thanks again for you quick reply.

I can't get it to work :cry: .
I've copied the sybdb.dll into the syswow64 folder and tried the parameters mentioned in your last post.
I always get the exception: "DBError: [20009] : Unable to connect: Adaptive Server is unavailable or does not exist

Did you need to do any changes to the server configuration as well to get it up and running?

Best regards,
Bastian

BastiFantasti
Fresh Boarder
Fresh Boarder
Posts: 8
Joined: 13.07.2015, 18:58

Re: FreeTDS Connection to MS SQL 2008

Postby BastiFantasti » 03.03.2016, 09:20

I did some further research and found out, that the ODBC interface wasn't able to connect to the MSSQL Server as well.

The reason was, that the "SQL Server Browser" service was not running on the database pc.
I started the service and everythings good now!

thanks again for your help.

Best regards,
Bastian


Return to “MS SQL Server”

Who is online

Users browsing this forum: No registered users and 1 guest