Skip to Content.
Sympa Menu

freetds - Re: [freetds] 'secure connection' to SQLserver. Integrated security, ssl?

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddyz77 AT tin.it>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] 'secure connection' to SQLserver. Integrated security, ssl?
  • Date: Fri, 09 Dec 2005 15:01:27 +0100

Il giorno ven, 09/12/2005 alle 13.37 +0100, W.H. van Atteveldt ha
scritto:
> > > Can I use a linux freetds client to connect to a mssql server that
> has
> > > the 'force protocol encryption' box checked?
> >
> > CVS version can either using gnutls or openssl.
> >
> > freddy77
>
> Excellent!
>
> I tested this immediately, and it seems to work. (But see below for two
> changes to 'configure' that I had to make to get it to compile).
>
> Thus, I test to connect to a test mssql server on my laptop (connecting
> via an ssh-tunnel) first without protocol encryption:
>
> [wva@swpc450 wva]$ tsql -S localhost -U sa -P <secret!>
> locale is "en_US.UTF-8"
> locale charset is "UTF-8"
> 1>
>

localhost?? No, when you use encryption you have to connect to server
directly.

> Then with protocol encryption (ie I checked the 'force protocol
> encryption' box in my mssql installation).
>
> [wva@swpc450 wva]$ tsql -S localhost -U sa -P <secret!>
> locale is "en_US.UTF-8"
> locale charset is "UTF-8"
> Msg 20004, Level 9, State 0, Server OpenClient, Line 0
> Read from SQL server failed.
> Msg 20014, Level 9, State 0, Server OpenClient, Line 0
> Login incorrect.
> There was a problem connecting to the server
>
> (see below for a snippet of /tmp/freetds.log)
>
> So probably I need to add something in my ~/.freetds.conf file, which is
> currently limited to:
>
> [global]
> tds version = 8.0
> port = 1433
>
> So can I use the conf file (or some other option) to ask for encryption?
> How would that work? Or do I need to do something with certificates,
> such as adding the root certificate of the certificate used by sql
> server to my openssl trusted certificates (which seems to be required on
> ms boxes)? Any hint on how I can do that?
>

In snapshot you will find a tds_ssl.html that help you on how to add
certificate to mssql. Is not sufficient to check the flag.

> Otherwise, it might be a problem to use openssl over an ssh tunnel,
> although I thought ssl is simply on tcp level, so it shouldn't bite. But
> what do I know of these things...
>
> Again, any help greatly appreciated!
>
> Wouter
>

Well, ssh crypt by itself... or you use ssh or you use ssl (either using
stunnel or similar or enabling encryption at mssql and FreeTDS side)

> ---
>
> Freetds.log:
>
> 12:22:50.736355 12374 (util.c:288):Starting log file for FreeTDS
> 0.64.dev.20051209
> on 2005-12-09 12:22:50 with debug flags 0xffff.
> 12:22:50.736540 12374 (iconv.c:195):names for ISO-8859-1: ISO-8859-1
> 12:22:50.736557 12374 (iconv.c:195):names for UTF-8: UTF-8
> 12:22:50.736567 12374 (iconv.c:195):names for UCS-2LE: UCS-2LE
> 12:22:50.736577 12374 (iconv.c:195):names for UCS-2BE: UCS-2BE
> 12:22:50.736587 12374 (iconv.c:361):iconv to convert client-side data to
> the "UTF-8" character set
> 12:22:50.736622 12374 (iconv.c:514):tds_iconv_info_init: converting
> "UTF-8"->"UCS-2LE"
> 12:22:50.736642 12374 (net.c:168):Connecting to 127.0.0.1 port 1433.
> 12:22:50.736912 12374 (net.c:673):Sending packet
>
> <packet containing plaintext login info>
>
> 12:22:50.737204 12374 (net.c:673):Sending packet
> 0000 02 01 00 4c 00 00 00 00-00 00 00 00 00 00 0a 00 |...L.... ........|
> 0010 00 00 00 00 00 00 00 00-00 00 00 00 00 75 74 66 |........ .....utf|
> 0020 38 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |8....... ........|
> 0030 00 00 00 00 00 00 00 00-00 00 00 04 01 35 31 32 |........ .....512|
> 0040 00 00 00 03 00 00 00 00-00 00 00 00 |........ ....|
>
> 12:22:50.737276 12374 (token.c:310):tds_process_login_tokens()
> 12:22:51.916515 12374 (util.c:119):Changing query state from IDLE to
> DEAD
> 12:22:51.916607 12374 (token.c:2240):tds_client_msg: #20004: "Read from
> SQL server failed.". Connection state is now 4.
> 12:22:51.916621 12374 (token.c:314):looking for login token, got 0()
> 12:22:51.916634 12374 (token.c:105):tds_process_default_tokens() marker
> is 0()
> 12:22:51.916645 12374 (token.c:108):leaving tds_process_default_tokens()
> connection dead
> 12:22:51.916655 12374 (util.c:119):Changing query state from DEAD to
> DEAD
> 12:22:51.916671 12374 (token.c:2240):tds_client_msg: #20014: "Login
> incorrect.". Connection state is now 4.
> 12:22:51.916711 12374 (mem.c:475):tds_free_all_results()
>

Mmm... you are not using TDS 8.0 (required for encryption)...

> ----
>
> Compilation problems:
>
> I did have some problems compiling the cvs version, however, probably
> because I'm such an idiot with compiling and installing things. What I
> did was:
>
> with_openssl=1 ./configure
> make
>

In snapshot there is an updated UserGuide with new options.

> With part of the configure output:
> > checking if openssl is wanted... yes
> > OpenSSL found in /usr/local/ssl
> Which looks good to me.
>
> (1) The first problem was that the include path found by configure is
> /usr/local/ssl/include/openssl; in this path it attempts to find
> openssl/ssl.h, effectively looking for the non-existing
> /usr/local/ssl/include/openssl/openssl/ssl.h. Thus, I added the line
>
> CFLAGS="$CFLAGS -I$ssldir/include"
>
> after line 20098 in configure.
>

An easier way is to set CFLAGS before configure like

CFLAGS='-O2 -g -I/path/to/whatever' ./configure --with-gnutls
This should be fixed in tomorrow snapshot

> (2) The next problem is that it says:
>
> /usr/local/ssl/lib/libcrypto.a(dso_dlfcn.o)(.text+0x37): In function
> `dlfcn_load':
> : undefined reference to `dlopen'
>
> A bit of browsing indicated that I might have to specify -ldl on the gcc
> command, which I inserted in the configure file in line 20117:
>
> NETWORK_LIBS="$NETWORK_LIBS -lssl -lcrypto -ldl"
>

You can set LFLAGS='-ldl' however this should be fixed in final
release...

> I'm sure this should really go somewhere else, but I'm not very good
> with makefiles and the like, and this seemed to work... There are a
> number of references to -ldl in the configure file already, but I have
> no idea what they do... sorry!

Don't mind! Thanks for testing and report.

freddy77






Archive powered by MHonArc 2.6.24.

Top of Page