Connecting to a MS-SQL Database using iODBC - Part 1

I took me a while to find out how to connect to a Microsoft SQL Express 2012 (MS SQL) database using ODBC. The main issue was to find and configure all the pieces of software. The chain of software that is required for a successful query is given below.

SQL Express (2012) (The database server - Win7 Ultimate)

|

MS ODBC Server

|

MS SQL Connection protocols

|

Windows Firewall

|

Network + Firewalls (The network cloud)

|

FreeTDS (MS SQL Driver)

|

iODBC

|

Perl (The client machine - FreeBSD)

This is part one of a two part blog.

SQL Express 2012
There is probably better install guides out there but here is some basic notes I took when install this software. I downloaded and tested with "SQL Server Express With Tools" (SQLEXPRWT_x86_ENU.exe).

Install Microsoft SQL 2012 Express with default options:

• During installation, ensure these instructions are followed:
  • On the setup screen titled- “SQL Server Installation Center”, select "New installation or add features to an existing installation"
  • On the setup section titled-“Feature Selection”, check everything and continue
  • On the setup section titled “Installation Rules”, fix any items marked with a red "X" symbol and continue
  • On the setup section titled- “Instance Configuration”, select "Named instance" and accept the default name, continue (eg WIN-JI41DKO0O6O\SQLEXPRESS)
  • On the Database Engine Configuration select windows authentication mode and continue (this may need to change after my next install as we really need SQL authentication and create a SQL user with correct permissions)
• During install an error occurs, retry and it goes away ;-0

 Add a SQL user

Click on Start -> Microsoft SQL Server 2012 -> SQL Server Management Studio and log into the SQL database created above using Windows Authentication.




In Object explorer expand Security and right click on Logins and Select New Login.







Select SQL Authentication and provide a new username and password. This is the user that Perl running on FreeBSD will use to login.






Enable SQL Authentication

Right right click on the database and select permissions. Select Security and change Server authentication from "Windows Authentication" to "SQL Server + Windows Authentication"





MS ODBC Server

 Click Start -> Control Panel -> Administrative Tools -> Data Sources





 Click on System DSN and select the SQL Server Driver (ie NOT the SQL Server Native Client)



Give it a Name and Description (neither of which is really important). In Server ignore the drop down as its useless buy type in (or paste in) the full name of the database server from when you installed it.



 Click Next, then select Connect to SQL Server to obtain default settings for the additional configuration parameters. Type in the SQL username password created above. 



 Then select all the default options. Finally hist "Test Data Source" and check if everything works.
 


The click Ok twice.


Click here to continue to part 2.