Need help connecting MSSQL via Soci

Today I was thought of creating a C++ app that connects to MS SQL server and do functions such as retrieving records and execute SPROCs, Then I found this Soci, a C++ library which helps our app to talk with DB, and I also found there is no specific backend-option in Soci for MSSQL unlike it has for Oracle and others, looks like, we could only talk to mssql via ODBC. however, I have been searching for a proper document/manual/reference which would give me a step by step instructions for connecting to MSSQL via soci, I wandered around Soci's mother-site but nothing I could find for MSSQL. (I have seen this

backend_factory const& backEnd = odbc; session sql(backEnd, "filedsn=c:\\my.dsn");

But, my DB's are hosted in a separate server, so, it would be hellpful if someone could shed some light on this:)

Solution

You can use ODBC to connect to sql server with Soci using a connection string like the following:

"DRIVER=libtdsodbc.so;SERVER=MYSERVER;PORT=1433;DATABASE=my_database_name;UID=username;PWD=password"

With the Session creation function (using odbc as per the Soci documentation).

Where:

MYSERVER is the address / name of the server you want to connect to.
1433 is the default port for sqlServer, this may be different in your case.
my_database_name is the name of the database you are trying to access

This should then be using FreeTDS on top of the ODBC driver. The authentication is interesting, by default FreeTDS will use sql authentication, unless there is a backslash in the user name then it will use Windows Domain Logon for authentication EG: "DOMAIN\username"

Also interesting is the FreeTDS connection string parameters.

Hope this helps!