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.

image_014

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

image_015

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

image_016

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”

image_017

 

 

MS ODBC Server

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

image_018

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

image_019

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.

image_020

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.

image_021

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

The click Ok twice.

Click here to continue to part 2.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s