Skip to Content.
Sympa Menu

freetds - [freetds] Setting up Trusted Connections from Linux to MS Sql Server 2008 [was Re: Building on Gentoo]

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Spencer Ogden <spencer AT spencerogden.com>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] Setting up Trusted Connections from Linux to MS Sql Server 2008 [was Re: Building on Gentoo]
  • Date: Thu, 06 Sep 2012 11:04:22 -0400

On 9/5/2012 1:57 PM, Mike Slifcak wrote:
Hi, Spencer.
I for one would be interested in your experience.
Would you consider writing up the steps that you took and your tips along the
way,
and present that to the FreeTDS authors, or copy the mailing list if possible?

Thank you,
-Mike Slifcak


Happy to Mike.

The following is very long. I apologize if the FreeTDS list is not the right location as it is only tangentially related. A mailing list probably isn't the best place to post, but I hope this OK. My apologies for any errors.

I found the information to be very scattered, so hopefully this will be somewhat helpful in getting it all in one place. I am not a Linux or Windows SysAdmin, so I am sure I am getting some stuff wrong, but this is what is working for me. I am working within a corporate network, so security was not of the utmost concern, so this may not be the most secure setup. The process was very piecemeal, making sure each component worked on its own before moving on. I will try to reflect the possible testing at each step.

Goal: Connect from a Python on a Gentoo machine to a SQL Server 2008 server which only accepts Trusted Connections (aka Windows Authentication).

Steps:
1) Install the required tools: kerberos, samba, freetds, unixODBC, python, pyodbc, sqlite
2) Setup kerberos and test authenticating against an Active Directory controller
3) Setup Samba and winbind to join the Linux server to the AD
4) Setup PAM to authenticate login sessions against AD and create kerberos tickets
5) Setup unixODBC to communicate with SQLite as a simple test
6) Setup FreeTDS to connect to SQL Server with kerberos credentials
7) Integrate FreeTDS driver in unixODBC
8) Show options for creating kerberos credentials
9) Password free connection from Python

1) Install required tools

I'm running a Gentoo install with the 3.4.9 kernel on AMD64
Install the following either using your distros package manager or compiling. Some versions are critical as features are new.
ntp 4.2.6_p5-r1
mit-krb5 1.9.4-r1
pam_krb5 4.5
samba 3.5.15
freeTDS 0.91
unixODBC 2.3.1
python 2.7.3
sqlite 3.7.13
sqliteodbc 0.87
pam_mount 2.11 (Optional) You can use this to mount Windows shares at login. Not covered below.

2) Setup Kerberos
The Kerberos config file is /etc/krb5.conf. You will need some info about your Active Directory setup.

* Realm = get with `echo %userdnsdomain%` on a windows machine already on the network
* IP of Domain Controller = On windows, run `nslookup` and enter the following where <Realm> is the previous answer
|_ldap._tcp.dc._msdcs.<Realm>

Your krb5.conf file should look something like the following. The capitalization of realms and domains is important. It would probably be better to use DNS names than IPs, but we do not have DNS setup, so I am counting on my admins not changing IPs anytime soon.
[libdefaults]
default_realm = <Realm>
ticket_lifetime = 36000
renew_lifetime = 604800
clockskew = 300 # The clocks on your Linux box and the DC must match, use ntp for that
fcc-mit-ticketflags = true
default_keytab_name = FILE:/etc/krb5.keytab

[realms]
# use "kdc = ..." if realm admins haven't put SRV records into DNS
<Realm> = {
kdc = <IP>:88
admin_server = <IP>:749
default_domain = <IP> }

[domain_realm]
# Alternate caps and punctuation
<Realm in lower case>= <Realm>
.<Realm> = <Realm>

[logging]
kdc = FILE:/var/log/krb5kdc.log
admin_server = FILE:/var/log/krb5admin.log
default = FILE:/var/log/krb5lib.log

You should also install and setup NTP to sync to the same clock that your DC uses. I am not sure, but I believe a DC also acts as an NTP server, you could just setup NTP to sync to your DC using the IP you got above.

Testing:
You now have the `kinit`, `klist` and 'kdestroy` commands. To test your setup, you should be able to do `kinit <username>` with your Windows username and password. If kinit returns nothing, you have success. You can verify this with `klist'.

kinit creates a ticket which expires after a certain amount of time, but can be used by other programs to interact with AD or SQL Server without using a password.

3) Setting up Samba

Good documentation here: http://wiki.samba.org/index.php/Samba_&_Active_Directory

Config file is /etc/samba/smb.conf. It should look like the following, with the info from above:
[global]
workgroup = <Realm>
realm = <Realm>
preferred master = no
server string = <Name for this server>
security = ADS
kerberos method = system keytab
dedicated keytab file = /etc/krb5.keytab
encrypt passwords = yes
log level = 3
log file = /var/log/samba/%m
max log size = 50
winbind enum users = Yes
winbind enum groups = Yes
winbind use default domain = Yes
winbind nested groups = Yes
winbind separator = +
idmap uid = 2000-20000
idmap gid = 2000-20000
template shell = /bin/bash

The idmap commands set the uid and gid ranges assigned to AD users. These should not conflict with existing users or groups.

Testing:
Run `testparm` to test your config. You will get a warning about "winbind seperator" but it is OK.

Now to join the domain. You will need a user with admin rights to do this. Run `net ads join -U <username>`.

Testing:
`net ads testjoin`
`wbinfo -u` should list all users in the domain

You now have Samba setup and have joined the AD Domain

4) Setup authentication against AD
The file /etc/nsswitch.conf sets where user credentials are checked against. There are more lines in the file, but below are the ones that need to be edited. "winbind" should be added to the end of each line.
passwd: compat winbind
shadow: compat winbind
group: compat winbind

hosts: files dns wins winbind
networks: files dns wins winbind

Your config might read "files" instead of "compat". Short is that local files are checked first, then winbind is used to check anything not found there.

Testing:
`getent passwd` should return your local /etc/passwd file, followed by all users on AD.
`getent group` should return your local groups followed by all AD groups.

PAM Configuration: Be very cautious editing PAM config. You can lock yourself out of the system. Always keep one terminal logged into root at all times, and test logging in with a separate terminal, testing both local and AD logins. Always make sure you can re-login as root. Backup your pam.d directory before edits.

PAM is still a bit of a mystery for me, but flow that works for me. All distros organize /etc/pam.d slightly differently, but here are the core lines:
auth required pam_env.so
auth sufficient pam_winbind.so
auth sufficient pam_unix.so nullok use_first_pass
auth required pam_deny.so

account sufficient pam_winbind.so
account required pam_unix.so

password required pam_cracklib.so retry=3
password sufficient pam_unix.so nullok use_authtok md5 shadow
password required pam_deny.so

session required pam_mkhomedir.so skel=/etc/skel/ umask=0022
session required pam_limits.so
session required pam_unix.so
session optional pam_winbind.so

The important parts are the pam_unix and pam_winbind lines. Ensure that those match in terms of "sufficient" and the arguments used.

Finally, edit pam_winbind.conf, this is in /etc/security on Gentoo. Edit or add the following lines:
krb5_auth = yes
krb5_ccache_type = FILE

This means that when an AD user logs in, a kinit will automatically be done, allowing their credentials to be passed on.

Testing:
You should now be able to login locally or through SSH to the linux machine using an AD username and password. A home directory should be create under /home/<DOMAIN>/<username>. After you login, run `klist`, you should see a ticket with your credentials.

There is more that can be done in terms of syncing groups with AD and having permissions work better between AD and Unix permissions, but that's beyond my present efforts.

5) Setup a simple test of unixODBC
This is a very simple test, but I was struggling with connecting with SQL Server, so I started simple. The two config files are odbcinst.ini and odbc.ini.

IMPORTANT: unixODBC does not deal well with whitespace in it's config files. Do not use any leading whitespace in either file.

For this test, setup as follows. Note that your library locations for the "Driver" and "Setup" lines may differ, but the file name should be the same. Note the "::::" indicate headers and are not part of the config files. The below two chunks go into two separate files, odbc.ini and odbcinst.ini:
::::::::::::::
/etc/unixODBC/odbc.ini
::::::::::::::
[Test]
Driver = SQLite
Database = /root/test.db
::::::::::::::
/etc/unixODBC/odbcinst.ini
::::::::::::::
[SQLite]
Description=SQLite ODBC Driver
Driver=/usr/lib64/libsqlite3odbc.so
Setup=/usr/lib64/libsqlite3odbc.so
Threading=2

Then, as root, run `sqlite3 test.db`. Paste in the following commands:
CREATE TABLE testtbl( one varchar(10) );
INSERT INTO "testtbl" VALUES('test');
.quit

You now have a test database at /root/test.db as indicated in the config file above.

Testing:
`echo "SELECT * FROM testtbl" | isql -b Test` should output the contents of the test database.

6) Setup FreeTDS
Set up FreeTDS as follows. I am connecting with SQL Server 2008. The server does not allow "SQL Authentication" only "Windows Authentication" aka "Trusted Connections". As far as I know there is only one instance running at the IP. I have not done any testing with other SQL Server setups.

Edit /etc/freetds.conf to add the following block. I am using all defaults in the [global] section.
[Test]
host = <SQL Server domain name like server.asdf.com>
port = 1433
tds version = 8.0

Testing:
Log in as an AD user. Run `tsql -S Test`. Enter "SELECT SYSTEM_USER" and on the next line enter "go" and enter. You should get back your AD username. This confirms that your login created a kerberos ticket, which freetds is now using to log into SQL Server. If you have a problem, run `kinit <username>`. If you get an error, there is a problem with your kerberos setup. If that works, rerun the tsql test. If it works then there is a problem with you samba/winbind setup. If your SQL Server allows SQL Authentication, then try testing with tsql and a username and password to verify that the connection without kerberos is working.

7) Integrate FreeTDS with unixODBC
Again, note that unixODBC does not support whitespaces in the config files, keep things simple. Edit your unixODBC config as follows:
::::::::::::::
/etc/unixODBC/odbc.ini
::::::::::::::
[Test]
Driver = FreeTDS
Server = |<SQL Server domain name like server.asdf.com>||
|Port = 1433
Database = <Default Database>
::::::::::::::
/etc/unixODBC/odbcinst.ini
::::::::::::::
[FreeTDS]
Description=TDS Driver (MS SQL)
Driver=/usr/lib64/libtdsodbc.so

Testing:
Same as the previous testing, log in with an AD user. Run ` echo "SELECT SYSTEM_USER" | isql K2DW`. Again this should return your AD user and not ask for any password.

8) Other options for kerberos
ktutil can be used to create a keytab file. A keytab stores a password in encrypted format such that it can be used to authenticate using kinit. The create a keytab, run `ktutil` and enter the following commands:
ktutil: addent -password -p <username> -k 1 -e rc4-hmac|
ktutil: wkt <filename>.keytab|

The keytab file can be used to initialize kerberos credentials without the password being revealed. The kinit command is `kinit <username> -k -t ||<filename>.keytab`. This will initialize the credentials for the current session if for instance you have a non-AD user which you want to use AD credentials for to allow DB access.

9) From Python
So if you have a server process that runs as a non-AD user, we can't rely on the login process to kinit kerberos credentials for us. We can do something like this using the keytab we created above:

|importos|
os.system('kinit sogden -k -t <filename>.keytab')
import pyodbc
con = pyodbc.connect('DSN=Test')
cur = con.cursor()
cur.execute('SELECT SYSTEM_USER')
print cur.fetchall()

||
|||




Archive powered by MHonArc 2.6.24.

Top of Page