FreeTDS Connection to MS SQL 2008

[BastiFantasti]

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]

@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]

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]

Hi and thanks again for you quick reply.

I can't get it to work .
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]

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