What is required to get OGR to work with SQL Server?

Question

All,

I've been having heaps of trouble getting OGR to connect to our SQL Server development environment. (read about it here and here)

SQL Versions: 2008, 2012
OS's: Windows 7, Ubuntu 12.04

My questions are:

1) What permissions does the user need in order to establish a connection to a SQL Server database with OGR?

2) Does anything need to be loaded on the SQL Server machine or application server itself?

I used to be able to connect even with QGIS, but that isn't working now either. (or here either)

Also: if you have a password with a ! in it, it will throw back a 'bash' error...fyi!

Ok thanks again, all!

Answer 1

I can write to SQL Server 2008 with GDAL 1.10.1 using below command. I'm using SQL Server auth entication and a user that has connect, create procedure, create table, create view, select and showplan. I suspect that I only need, connect and create table for below.

Although I have also had to delete the geometry columns and spatial ref system tables at times 'manually'. At the moment these do not exist but this command works.

U:\>ogr2ogr -overwrite -nln "LCCGIS.CAD_Parcels_JSON" -f "MSSQLSpatial" "MSSQL:server=SQL2008P1\MSSQLSERVER32BIT;database=GISTEST;Uid=lccgis;pwd=password" jsonURL -lco "DIM=2"