How to connect to SQL Server using FreeTDS ODBC
Solution 1
Start by confirming that you're editing the correct configuration files.
You can confirm FreeTDS's view of the world with:
$ tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v1.1.11
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: no
Sybase binary compatibility: yes
Thread safety: yes
iconv library: yes
TDS version: 7.3
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: yes
OpenSSL: yes
GnuTLS: no
MARS: yes
This shows that the System-wide freetds.conf
file will be at the path /usr/local/etc/freetds.conf
, although you can have your own user-specific version at ~/.freetds.conf
.
If you're trying to connect to an SQL Server on your network, fred.example.com
, you can create an alias for it in freetds.conf:
[fred]
host = fred.example.com
port = 1433
tds version = auto
client charset = UTF-8
The [fred]
alias is not case-sensitive. TSQL can connect to it with any of the following:
$ tsql -S fred -U "FRED\YourSQLUserName" -P "YourSQLPassword"
$ tsql -S FRED -U "FRED\YourSQLUserName" -P "YourSQLPassword"
$ tsql -S FrEd -U "FRED\YourSQLUserName" -P "YourSQLPassword"
... and so on.
Once you've established that FreeTDS is working you can move on to ODBC. You can check ODBC's view of the world with:
$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/YourUserName/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
Start by editing the /usr/local/etc/odbcinst.ini
file (which start out empty) and adding the following:
[FreeTDS]
Description=FreeTDS Driver for Linux & MSSQL
Driver=/usr/local/lib/libtdsodbc.so
Setup=/usr/local/lib/libtdsodbc.so
UsageCount=1
Next, you can add a System-wide data source for Fred in /usr/local/etc/odbc.ini
or a user-specific one in ~/.odbc.ini
:
[fred]
Description = Test to SQLServer
Driver = FreeTDS
Servername = fred
Note that the Servername = fred
here points to the [fred]
in freetds.conf
. It's not case-sensitive, either, but you shouldn't have one called fred
and the other called daphne
.
Now you should be able to connect using ODBC with:
$ isql fred "FRED\YourSQLUserName" "YourSQLPassword"
Hope this helps.
Solution 2
Part of your confusion is likely due to your naming everything "TEST". This works for me:
freetds.conf
[MYMSSQL_SERVER]
host = 192.168.0.179
port = 49242
tds version = 7.0
odbcinst.ini
[FreeTDS]
Description=FreeTDS ODBC
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
odbc.ini
[TEST]
Description = Test to SQLServer
Driver = FreeTDS
Servername = MYMSSQL_SERVER
isql
needs the DSN name
gord@xubu-Inspiron-1720:~$ isql TEST sa sapassword
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> SELECT @@SERVERNAME
+---------------------------------------------------------------------------------------------------------------------------------+
| |
+---------------------------------------------------------------------------------------------------------------------------------+
| GORD-HP\SQLEXPRESS |
+---------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
tsql
needs the FreeTDS server name
gord@xubu-Inspiron-1720:~$ tsql -S MYMSSQL_SERVER -U sa -P sapassword
locale is "en_CA.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> SELECT @@SERVERNAME
2> GO
GORD-HP\SQLEXPRESS
(1 row affected)
Note that if you are only using ODBC then you don't need an entry in freetds.conf. The trick is to have the DSN use Server
instead of Servername
. This works, too:
odbc.ini
[TEST_ODBC_ONLY]
DRIVER=FreeTDS
SERVER=192.168.0.179
PORT=49242
TDS_Version=7.0
which isql
likes just fine:
gord@xubu-Inspiron-1720:~$ isql TEST_ODBC_ONLY sa sapassword
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> SELECT @@SERVERNAME
+---------------------------------------------------------------------------------------------------------------------------------+
| |
+---------------------------------------------------------------------------------------------------------------------------------+
| GORD-HP\SQLEXPRESS |
+---------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
And, by extension, with pyodbc you don't even need a DSN if you use a DSN-less connection:
connection_string = (
'DRIVER=FreeTDS;'
'SERVER=192.168.0.179;'
'PORT=49242;'
'TDS_Version=7.0;'
'UID=sa;PWD=sapassword;'
)
cnxn = pyodbc.connect(connection_string)
crsr = cnxn.cursor()
print(crsr.execute("SELECT @@SERVERNAME").fetchval())
# GORD-HP\SQLEXPRESS
Related videos on Youtube
ny_coder_dude
Updated on June 04, 2022Comments
-
ny_coder_dude almost 2 years
I am trying to connect to my company's SQL Server Databases via my MacBook and have followed the steps outlined here: https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Mac-OSX but keep getting the following error when I get to the following step:
Check that all is OK by running
isql TEST myuser mypassword
. You should see the following:+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+
I already verified that the following works:
Test the connection using the tsql utility, e.g.
tsql -S TEST -U myuser -P mypassword
. If this works, you should see the following:locale is "en_US.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" 1>
The odbcinst.ini and odbc.ini configuration files are both in the same directory.
MacBook-Pro: myname$ odbcinst -j unixODBC 2.3.7 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /Users/myname/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8
odbcinst.ini file config:
[FreeTDS] Description=FreeTDS Driver for Linux & MSSQL Driver=/usr/local/lib/libtdsodbc.so Setup=/usr/local/lib/libtdsodbc.so UsageCount=1
odbc.ini config:
[TEST] Description = Test to SQLServer Driver = FreeTDS Servername = ServerName
freetdf.conf
# $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden Exp $ # # This file is installed by FreeTDS if no file by the same # name is found in the installation directory. # # For information about the layout of this file and its settings, # see the freetds.conf manpage "man freetds.conf". # Global settings are overridden by those in a database # server specific section [global] # TDS protocol version tds version = auto # Whether to write a TDSDUMP file for diagnostic purposes # (setting this to /tmp is insecure on a multi-user system) ; dump file = /tmp/freetds.log ; debug flags = 0xffff # Command and connection timeouts ; timeout = 10 ; connect timeout = 10 # If you get out-of-memory errors, it may mean that your client # is trying to allocate a huge buffer for a TEXT field. # Try setting 'text size' to a more reasonable limit text size = 64512 # If you experience TLS handshake errors and are using openssl, # try adjusting the cipher list (don't surround in double or single quotes) # openssl ciphers = HIGH:!SSLv2:!aNULL:-DH # A typical Sybase server [egServer50] host = symachine.domain.com port = 5000 tds version = 5.0 # A typical Microsoft server [TEST] host = ServerName port = 1433 tds version = 7.3 client charset = UTF-8
My command and the output:
isql TEST myuser mypass -v
[IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified [ISQL]ERROR: Could not SQLConnect-
Gord Thompson over 4 yearsAre you telling us that
tsql -S MYMSSQL ...
works for you? If so, then you're not giving us the whole story because nowhere in any of your configuration files is there an entry namedMYMSSQL
. Please edit your question to describe exactly (and concisely) what you are really doing, what works, and what doesn't. -
FlipperPA over 4 yearsAlso, the
tds version = 8.0
isn't invalid. This won't prevent the connection, but you should choose a propertds version
, depending on your version of FreeTDS. See the second table on this page: freetds.org/userguide/choosingtdsprotocol.htm -
ny_coder_dude over 4 years@GordThompson I updated the question - not sure how that changes anything as you could have inferred from my configuration files that I was using TEST in the commands instead of MYMSSQL.
-
ny_coder_dude over 4 years@FlipperPA I updated it to the correct version, 7.3, but still no luck
-
FlipperPA over 4 years@dir101 Yeah, that wouldn't fix this problem but it will save you headaches down the road. :)
-
-
ny_coder_dude over 4 yearsI am trying to connect to a SQL Server named TEST, I just used the example commands from the link above. It still is not working. I updated the question.
-
AlwaysLearning over 4 years@dir101 You still have
Servername = ServerName
in your question'sodbc.ini
file andhost = ServerName
in yourfreetds.conf
file. Do you have an SQL server namedServerName
on your network? -
ny_coder_dude over 4 yearsI just masked the actual server's name with ServerName. Wherever you see ServerName in my files I have the name of the server.
-
Gord Thompson over 4 years@dir101 - You need to be more precise in your descriptions. When you say "the name of the server" do you mean the TDS name (as defined in freetds.conf) or the DNS name (e.g., someserver.example.com)?
-
Gord Thompson over 4 years@AlwaysLearning - This answer would be considerably less confusing if you didn't use
MYMSSQL
for the name of everything. Perhaps call the ODBC DSNMYMSSQL_DSN
(in odbc.ini), the FreeTDS serverMYMSSQL_SERVER
, and usehost = mymssql.example.com
to help disambiguate the different names. -
AlwaysLearning over 4 years@GordThompson That was the name offered by the OP in their original question. They have since edited their question to use TEST instead.
-
Gord Thompson over 4 years@AlwaysLearning - Yes, I know, but my point was that just because they used the same name for everything and got themselves all confused doesn't mean that your answer has to do the same.
-
ny_coder_dude over 4 years@GordThompson can you guys please help me resolve this? It is still not working
-
ny_coder_dude over 4 years@GordThompson I have updated my question with pertinent info. Is there any other info you guys need?
-
AlwaysLearning over 4 years@dir101 In your question you're saying
freetdf.conf
file... is this actually the name you've used instead offreetds.conf
? By default this file should be in the/usr/local/etc
folder unless you've overridden it with the--sysconfdir
option. Alternatively you may have one in your home folder as~/.freetds.conf
(note the.
beforefreetds.conf
).