Connect to SQL Server Spatial

[Carto]

I am trying to connect to a SQL Server Spatial datasource in Autodesk Infrastructure Studio 2012. I can connect to the datasource and the test connection is ok. However when I open the data source the feature class is empty with a warning. Would anyone be able to help resolve this issue? Thanks

[jackie.ng]

If your SQL Server database has no tables with geometry/geography columns, then you can't actually create any layers from that database.

You should also check if your specified user has permission to access the tables you were expecting to see in the Feature Class list.

- Jackie

[Carto]

Thanks for your help. I think it is a permisson issue as I have tried to connect to the database via FDO toolbox and an error is displayed that I am unable to access the system database 'model'. I will try and contact the DBA.

[gluckett]

This is probably related to the AIMS bug that requires access to all databases regardless of user - if you log in with SA you will not have this issue.

[Carto]

Thanks to you both for replying. If I log on with SA I do not have this issue and all the tables are visible. I am, however, not the database administrator (it is a corporate mapping dataset) and I am not sure how comfortable they will be in issuing a permanent log on with such a high level of permission. Do you know if there is a work around for this? Thanks

[foxeec]

In my experience, i have my DBA give me all permissions when i first connect to the SQL Server, then after I'm connected, he'll start locking down my permissions until i have only the tables i need.

Lorrie

[gluckett]

Yes, exactly, but there is a bug in AIMS that stops users who cannot
connect to all databases in some way. I haven't tested AIMS 2015 yet to
see if it is fixed.

[gluckett]

Yes, the bug is still there in 2015. 

[Carto]

I am also experiencing the same connection problems in Autodesk Map3D 2013. My IT department are really reluctant to increase my permissions without further information although I have demonstrated in both applications that logging in with sa solves the connection problems. Would anyone be able to provide details of the bug in AIMS? Thanks.

[gluckett]

Check out this thread. Bruno fixed the problem with the dll for sql server.
http://osgeo-org.1560.x6.nabble.com/Fwd-OSGeo-Discuss-Why-does-OSGeo-FDO-Provider-for-SQL-Server-Spa...

You will have to put the dll in your FDO folder under BIN in AutoCAD Map or
AIMS Server depending on your software.

Unfortunately this fix was not rolled into the products for 2015.

[DustinEarls]

I am using Studio 2012 and can't make a connection to any sql instances.

I installed a local sql express 2008 that I have permissions to all data tables and still can't connect.

The error is:

An exception occurred in FDO component.
RDBMS: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

Any help would be greatly appreciated.

[Carto]

Just wanted to say thanks for your suggestion. I haven't replied as I still haven't received permission from our IT department to download the dll files, hopefully I will be able to test your suggestion soon.

[santos.bravo]

Hi Carto,

SQl is a headache, but surmountable.

Try this:

1. Grant Select acces to all graphic tables in the catalog

USER [databaseName]
GO

GRANT SELECT ON schemaName.tableName TO userName; -- do this foreach table
GO

2. Authorize schema, only if you are using a different schema to dbo.

USE [databaseName]
GO

ALTER AUTHORIZATION ON SCHEMA::[schemaName] TO [userName]
GO

3. Change user to owner of database

USE [databaseName]
GO
EXEC sp_addrolemember N'db_owner', N'userName'
GO

EXEC sp_addrolemember N'db_ddladmin', N'userName'
GO

4. Set public access to MASTER database

use [master]
GO

GRANT SELECT TO PUBLIC
GO

GRANT SELECT TO userName
GO

5. This is the most difficult to get permission from DBA

set this permission foreach database in the tree of SQL Server

GRANT SELECT TO PUBLIC
GO

if you do not get prior permission,  then user MapGuide Maestro (it's most powerfull than infrastructure studio), and open database connection as XML and edit 

download here: http://trac.osgeo.org/mapguide/wiki/maestro

and do this:

Change in the DatStore secction the "Value" for databaseName.

this should work with.

good luck!

[Carto]

Thanks again for your help but I am still not having any luck gaining permission to change anything in the corporate SQL server settings. I did try editing the XML in Maestro as you suggested but can I just double check should I be entering 'Master' as the value?

[gluckett]

Some questions:
What is the name of the table you are connecting to?
Does it already have spatial data? (I.e. Geography or Geometry columns)
What is the name of the database you are connecting to?
Do you have full access to the database?

[Carto]

Hi, I am trying to connect to some OS Mastermap tables which do have spatial data. The problem is that I am connecting via a user which does not have full dbo permissions and I am just trying to find a work around without getting full dbo rights as my IT department are reluctant to grant them.

[gluckett]

So the database is:
"master"
the table is:
"dbo.whatever"

once your IT has given a SQL SERVER USER access (not you "windows password" but a SQL Server USER) to the table, you can connect to it with Studio/Maestro - you should always use SQL Server Users with AIMS rather than Windows users.

[santos.bravo]

Hi,

My personal suggestion, move the tables to another different database of "MASTER" master is a proprietary database for SQL SERVER, should only be used by the DBMS. Is not strange that IT does not give you permission on this database.

Have doubts about how to create a new database? ..

[Carto]

Sorry I think I have confused you the tables are not in the database called master they are in a test database environment I just don't have the correct permissions to access them, I will keep trying. Thanks again for your help.

[Carto]

Hi, it was just to give a quick update. I finally managed to connect to SQL Server data in AIMS 2012 (without having sa admin permissions) by creating views to the database tables owned by my user and not the dataowner. Thanks again for all your help and suggestions.