how to make shapefile in geometry column of ms sql?

Posted on

Question :

I was tried ogr2ogr command like this.

ogr2ogr -f "ESRI Shapefile" "C:tempsqlexport.shp" "MSSQL:server=[RemoteServerIp];database=[dbname];User Id=[user];Password=[password];trust_connection=yes;" -sql "SELECT [geometrycolumn].STAsBinary() FROM [tableName] WHERE [indexColumn] = 1;" -overwrite

but I saw error message.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

I don’t know what is wrong.

Answer :

You have conflicting login options in your connection string.

There are two main types of authentication:

  1. Windows Authentication – using your (or service account’s) Windows Active Directory credentials. This option does not require a user ID and password to be specified. This is designated by trust_connection=yes

  2. SQL Authentication – a login made on SQL Server which would require a user ID and password to be used. This is designated by User Id=[user];Password=[password]

So you need to remove the trusted_connection=yes if you’re using SQL Authentication, or if you want to use Windows credentials then remove the User Id and Password portion of the connection string and check to make sure the account running this exists has proper permissions setup in SQL Server for this domain.

The issue is that you are running your GIS programme from a domain that is not trusted by the domain in which SQL Server resides.

That are numerous methods you can use to correct this. You should discuss with your network admin/sql server admin as to the best approach.

  1. Create a SQL Server Account and use those credentials to connect to the SQL Server <– this might be the easiest
  2. Trust the domain in which your GIS Programme is running <–I would be loath to do this

Leave a Reply

Your email address will not be published.