I am using ogr2ogr from OSGeo4W on my Windows 7 box to import a shapefile into a SQL Server database on one of my servers. I was able to successfully complete the conversion using shp2sqlserver, but I can't figure out why it fails when I try the same thing with ogr2ogr directly.
Here's the syntax of my ogr2ogr command:
ogr2ogr -f MSSQLSpatial "MSSQL:server=[server];database=[dbname];User Id=[uid];Password=[pwd]" C:\Path\to\file\shapefile.shp
When I run this, it fails first with an error saying "Cannot open database [dbname] requested by the login. The login failed." Next is another error that the "MSSQL Spatial driver doesn't currently support database creation. Please create database with the Microsoft SQL Server Client Tools."
I have checked and double-checked that the user id and password given works properly, that there is already a database with the name given (I substituted [dbname] here to speak in generalities), and that the database is owned by the user.
As mentioned, I was able to do this with shp2sqlserver so I don't think it's my database setup that is the problem. Is there a problem with my connection string? Is there some peculiarity within ogr2ogr that I'm not aware of?
I am using a Windows 7 machine connecting to SQL Server 2008 R2. Gdal is version 1.9.1.
Best Answer
The ODBC documentation suggests keywords UID and PWD instead of User ID and Password.