Federation Server(ODBC/MSSQL wrappers) running under Windows Authentication

Federation Server(ODBC/MSSQL wrappers) running under Windows Authentication

---

 

 

Windows Authentication

 

Windows NT 4.0 domains will use NTLM to perform authentication. And Windows domains members running Windows 3.11, 95, 98, NT 4.0 will use NTLM to verify the identities of their membership.

 

[NTLM-Auth.png] 

Windows 2000 and Windows Server 2003 domains will use Kerberos version 5 by default to perform mutual authentication with members of Windows 2000, Windows Server 2003 and Linux/Unix with Kerberos client.

 

[Kerberos-Auth.png] 

 

 

ODBC/MSSQL Wrappers running under Windows Authentication

NTLM:

There are 2 scenarios when we want our odbc/mssql wrappers to access remote data source under NTLM authentication, such as Microsoft Access file, Excel file, Flat file data source.

 

Scenario 1:

[NTLM-S1.png]

When the remote data sources(MS Access mdb files for e.g.) were shared through tranditional Windows network in workgroups, we can config matchines as described below(only DSN is configured on Federation Server side), so that wrappers can access those files under control. It is described like a tree, only with configurations from root to leaf can make it work.

[DNS-Conf.png]

 

 

Scenario 2:

[NTLM-S2.png]

While the remote datasources is on a Linux/Unix machine, and shared with Windows, where Federation Server is running, through Samba. The user name and its password on the Samba side should be same as the user and password that start the db2 service.

 

For example, user db2inst  starts DB2 service, and its password is PASSWORD1, now, in order to access data sources(Access file for e.g.) on Samba, we should create an user named db2inst in advance, and create a password for Samba's authentication:

# smbpasswd -a db2inst

New SMB password:

Retype new SMB password:

we should type PASSWORD1 as the New SMB password here.

 

Here lists a test case here to show how Federation Server access a Microsoft Access file on a Samba server. Before do it, we should register a DSN named 'rsc' on the Federation Server side, and 'sc' is the database name, and the remote file path is "//192.168.0.33/share/sc.mdb".

 

 

Scenario 3:

[NTLM-S3.png]

Scenario 4:

[NTLM-S4.png]

To the 2 scenarios above, Linux can mount the samba file system to local file system, so that Federation Server on Linux/Unix can access those data source from Samba as they are on local file system.

# mount -t smbfs -o username=xxx //192.168.0.11/share /mnt/smb

Password:

Type password for the username on the remote OS for authentication, then every thing is transparency to Federation Server.

 

 

Kerberos:

Overview of the Kerberos Protocol

The Kerberos authentication protocol provides a mechanism for mutual authentication between a client and a server, or between one server and another, before a network connection is opened between them. Users can access multiple services with ticket-granting tickets got through once authentication, that is Single-Sign On(SSO).

 

This is the whole process of authentication when a Client in a domain request a service on a Server in the same domain:

0. Key Distribution Center(KDC) and its clients share their private keys. In an other word, only the conrresponding user and the KDC know the key to the user.

1. Client posts a Ticket Request to obtain a ticket-granting ticket(TGT).

2. KDC sends TGT to the client.

 

The transmission in step 1 and step 2 is encrypted with the private key between Client and KDC, so only the legal client can decode to get the TGT. Client will use the TGT to request a service ticket.

 

3. Client uses the TGT to post an other ticket request to obtain a service(provided by Server) ticket. The service ticket includes a Session Key(SK) which will be used to perform authentication between Client and Server. User name, IP, valid date, time stamp and so on, are also included in the ticket, which are used for Server to verify the Client's identity.

4. KDC returns the ticket(encoded with the private key of Server, because this is secret to the Client) and Session Key(encoded with Client's private key) to Client.

5. Client can only decode to know the Session Key, because it doesn't know the key between Server and KDC. When Client accesses the service on the Server, it should send the ticket got from KDC just now to pass the authentication, other information, such as client user name, ip....  is encrypted with the Session Key.

6. The Server can decode the ticket by its private key, so that Server can get the Session Key, then validate the Client's identity.

7. After the authentication, the Server will decide whether provide service to the Client or not.

 

[KerberosPrinciple.png] 

Federation Server access Data Source within a domain

As the Federation Server and Data Sources are deployed in a domain, the protocal act like this( they all are installed on Windows ):

0. db2admin provides his username and password to verify his identity in the domain, then starts the DB2 service on Federation Server side. db2admin will get a Ticket-Granting Ticket(TGT) from the KDC;

1. When db2admin want to access SQL Server whose Server Authentication is configured with Windows Authentication mode, he send a request with his TGT to KDC to get a service ticket for SQL Server Authentication;

2. KDC verify db2admin's identity and then send encrypted Session Key and Ticket to to db2admin, the Ticket is used for SQL Server;

3. Now, db2admin send request to remote SQL Server for service, he shows the Ticket  got from KDC to pass SQL Server's authentication;

4. Only if passed the authentication, db2admin can access SQL Server service.

 

Scenario 1:

[Kerberos-S1.png]   

In this scenario, remote data sources, such as Microsoft Access mdb files, were shared in domain, just permit db2admin(who calls ODBC APIs) to access is OK, but don't need tell the remote machine db2admin's password, because the authentication is performed by the domain controller, not the remote host computer.

 

For example, our mdb file is shared in a directory named "share", configure its permissions as below, Windows will prompt a dialog to verify whether you have tha privilage to change this setting or not.

 

[Kerberos-Share.png] 

If passed this authentication, user db2admin will get permission to access this dirctory.

 

[Kerberos-ShareProp.png] 

Following similar steps to set NTFS security, so that Federation Server can access data sources in the domain under control.

 

Scenario 2:

[Kerberos-S2.png]

Scenario 3:

[Kerberos-S3.png]

In this scenario, we can mount the shared directory which contains data source files, pay attention to the 'krb' option in the command line:

# mount -t smbfs //192.168.0.33/share /mnt/smb -o krb,username=db2admin

Warning: kerberos support will only work for samba servers

Password:

Type the password for db2admin, then Federation Server can access the data source files as they are in local file system.

 

Scenario 4:

[Kerberos-S4.png]

As we can see the Warning message in the scenario above, we can not mount a Windows share directory in Kerberos authentication circurmtances. Federation Servers(ODBC/MSSql) need ODBC drivers' support to access these data sources, but currently DataDirect's Connect for ODBC driver does not support Windows Authentication from a UNIX or Linux platform. So this is a limitation.

 

Or we need the Linux/Unix support CIFS File System, and

# mount -t cifs -o username=domain/user,password=passwd //ip/share /mnt/smb

to mount the share directory in the Windows Domain.

ref to: http://blog.csdn.net/liwenqiu/archive/2007/05/24/1623615.aspx

 

Scenario 5:

[Kerberos-S5.png]

For SQL Server, which we want to access it using Windows Authentication, we should chose to use Window NT authentication, when configure ODBC Data Source Node (DSN):

 

[KerberosODBC.png]

So Federation Server will access this node(SQL Server data source) with db2admin's(who calls ODBC APIs) status.

NOTE: When we want to access SQL Server with its native user, we cannot perform through this DSN.

 

Scenario 6:

[Kerberos-S6.png]

In this scenario, we need ODBC driver's support, but currently DataDirect's Connect for ODBC driver for SQL Server does not support Windows Authentication from a UNIX or Linux platform. This functionality is on the product roadmap for a future release of the Connect for ODBC SQL Server driver.

 

 

//

// some scenarios need  more configuration to detail

//

Access MS Access mdb files in domain:

In a domain whose authentication based on Kerberos[1], all the authentication is performed by the domain controllers.

Controllers can indicate:

1>which computers are members of the domain;

2>which users can access this domain;

3>how to organize the users and computers;

4>which user can access the domain through which computer;

..

..

..

Computers (members of domains) can indicate:

1>which domain user can access this computer through network;

2>which domain user can access my shared files;

3>which domain user start my SQL Server service;

..

..

..

So, if remote MS Access mdb files were shared in domain, just permit db2admin(who calls ODBC APIs) to access is OK, but don't need tell the remote machine db2admin's password, because the authentication is performed by the domain controller, not the remote host computer.

 

Access SQL Server use Windows Authentication:

(1)when logon SQL Server through Windows(domain) user, we should register a distinct DSN with using Windows Authentication configured, or "Integrated Security=SSPI" be included in connection string when call SQLDriverConnect[5], and the wrapper will use db2admin ( who calls ODBC APIs ) to access SQL Server. USER MAPPINGs will not work.

 

(2)wrappers can impersonate as the user indicated in USER MAPPINGs to access SQL Server without the users' password, only when db2admin( who calls ODBC APIs ) has SeTcbPrivilege[4]. There are Web applications which based on back-end SQL Server solve the problem by this way[2]. This solution requires Windows Server 2003 + VS7.

 

 

References:

[1]Windows 2000 Kerberos Authentication

http://www.microsoft.com/technet/prodtechnol/windows2000serv/deploy/confeat/kerberos.mspx

[2]Kerberos :

http://www.microsoft.com/china/technet/prodtechnol/windowsserver2003/technologies/security/constdel.mspx

[3]Exploring S4U Kerberos Extensions in Windows Server 2003:

en http://msdn.microsoft.com/msdnmag/issues/03/04/SecurityBriefs/default.aspx

cn http://www.microsoft.com/china/msdn/library/security/SecurityBriefs.mspx?mfr=true

It provides a sample code on how to use LsaLogonUser().

[4] NTSTATUS LsaLogonUser ( ... )  -> http://msdn2.microsoft.com/en-us/library/aa378292.aspx

[5]Connection string for MS SQL Server(.NET 1.1)

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.71).aspx