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

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()

||
|||