SQL Server Requires Connect to all Databases on Server

[gluckett]

Within the MapGuide Environment, SQL Server provider requires the SQL Server username to be able to connect to all databases, including "master".

This is very difficult to give a user this much power when connecting to SQL Server to an Enterprise SQL Server with a lot of live applications.

[jng]

Are you referring to the listing of databases that a user can connect to?

[jng]

The database listing query is currently something like this (according on SQL Server Profiler):

select name
 from master.dbo.sysdatabases S 
 where databasepropertyex(name, 'Status')='ONLINE'
 order by name collate latin1_general_bin asc

Based on (​https://stackoverflow.com/questions/9506927/how-to-find-databases-which-accessible-to-me-in-sql-server), if we want to cut this off to only databases the specified login can access, then the query should actually be:

select name
 from master.dbo.sysdatabases S 
 where databasepropertyex(name, 'Status')='ONLINE' and HAS_DBACCESS(name) = 1
 order by name collate latin1_general_bin asc

[jng]

Possibly related to #876

[gluckett]

Yes, the user requires "connect" to all databases currently. This is not feasible for large scale SQL Server implementations.

For example, the user may only be "owner" of one database, but the the connection in Maestro or Studio fails when connecting to SQL Server since it cannot "connect" to the other databases on the server. If we can limit to only those databases the user has access to - and yes it's like Ticket #876

[crispinatime]

We have applied the #876 fix by Bruno on our own builds before with success (replacing the DLL in AutoCAD and MapGuide deployments) -- it would be great to have a fix finally make it to trunk and release (and a release made available in downloads)

[jng]

Only enumerate databases the current user is allowed to access (using HAS_DBACCESS(name) = 1)

[jng]

v2 patch (now with unit test)

[jng]

In 7555:

Modify datastore query to use HAS_DBACCESS to cut off inaccessible databases.

Fixes #933

[jng]

In 7615:

Merged revision(s) 7534-7563 from trunk:
Fix a problem that the SHP file cannot be read if the SHP file has been opened for writing.

Reason:
If one connection is writing to the SHP file and the other connection connects to the same SHP file for reading and writing,
ShpFileSet::GetSpatialIndex(...) function will not work properly. This function will close the idx file and try to delete it.
But the deletion will not be performed since the idx file is locked at this moment. So "mSSI" member variable holds the invalid
file handler after the CloseFile() function is called. Later, "mSSI" is used to manipulate the index file, an exception is thrown.

Fix:
If the idx file cannot be deleted, "mSSI" member is deleted and set to NULL. Later, "mSSI" member will be created again if its value
is NULL.
........
#931: WMS Connection returns SSL connect error
Currently, the SSL version we set for curl is 1, which is CURL_SSLVERSION_TLSv1. Now we encounter a case that the WMS server is using TLS 1.2, but we get error CURLE_SSL_CONNECT_ERROR when connecting to this server. It works when setting SSL version to CURL_SSLVERSION_DEFAULT. I haven't found the root cause yet.
I submit the change to make it works. We may need to add the SSL version to configuration document later.
........
#932 WFS provider: value of number attribute is changed

When connecting to a WFS server who has an attribute in xsd:long format, the value which is bigger than 2147483647 (e.g. 2555555556) is shown incorrectly in Map3D (2147483647).

It is because we use wtol to convert a string a long number. The max value is 2147483647. To fix the issue, we need to add a new method ToInt64 in class FdoStringP.
........
#932 WFS provider: value of number attribute is changed

Continue to fix #932. Use new API FdoStringP::ToInt64 in method FdoXmlFeatureReaderImpl::GetInt64().
........
#934: Fix test baseline for GenericRdbms test suite

• General: Remove whitespace in various master text files that were failing content comparisons in various test cases
• MySQL:
  • MySqlReaderTest::TestExceptionalCase: Fix incorrect case on SQL query
• ODBC:
  • OdbcConnectionUtil::SetupMySqlDSN: Re-enable MySQL DSN registration. The test ODBC test suite for MySQL passes with the current MySQL Connector/ODBC, so I don't know why DSN registration was disabled.
• SQLServerSpatial:
  • SqlServerConnectTests::do_rdbi_connect: Change the connection string to use Uid/Pwd instead of Trusted_Connection. This allows us to run this particular test case against SQL Server 2017 on Linux. The old test has been #ifdef'd out through a new SQL_SERVER_XPLAT preprocessor macro that is defined by default (meaning Uid/Pwd is now tested by default)

Current suites with known test failures as of this commit is now:

• MySQL: 1 (down from 3)
• ODBC (MySQL): 0 (down from 49)
• PostgreSQL: 2 (down from 4)
• SQLServerSpatial: 2 (down from 6)

........
#934: Fix SchemaMgrTests.testConfigError failing for SQLServerSpatial. The configuration document being loaded (and expecting to fail) failed with a different error because the configuration document was not fed through OverrideBender.xslt (like other test configuration documents). This meant that the test case was trying to load OracleProvider.dll causing the test to fail with a different error than the one expected.
........
The current UpdateVersion.exe bombards UAC prompts every time it is run. Apparently just building a new version from source produces an UpdateVersion.exe that does not have this annoyance.

Fixes #667
........
Fix xalan build failure on Debug|x64
........
Fix output/intermediate path for UnitTestPostGIS to be consistent with other GenericRdbms-based unit test executables.
........
#934: The cause of all the content comparison test failures is due to inconsistency in how UnitTestUtil::PrintException outputs exception messages in release and debug modes. This submission establishes the correct baseline for master content and ensures UnitTestUtil adheres to it in both release and debug.
........
#934: Clean up apply_schema_test6_master.txt it had debug-only exception messages
........
#934: Update LogicalPhysicalFormatter.xslt to scrub out file/line number prefix on error messages. Debug FDO exceptions will carry this information and isn't going through UnitTestUtil::PrintException so they are being written out to schema content, failing the comparison tests.
........
#934: Update LogicalPhysicalFormatter.xslt only scrub out the file/line number if it is actually present in the error message.
........
Modify datastore query to use HAS_DBACCESS to cut off inaccessible databases.

Fixes #933
........
Modify UnitTestUtil::CreateDB to allow creating data stores without FDO metadata tables.
........
Fix SQL Server date columns not appearing in schema.

Patch by Bruno Scott. Unit test by me.

Fixes #875
........
Use locale-independent date format for handling dates in SQL Server.

Patch by Bruno Scott.

Fixes #766
........
Mark as read-only data properties whose mapped SQL Server physical columns are computed.

Includes unit test to verify computed columns come out as read-only data properties.

Fixes #920
........
OGR Provider changes:

• Make sure unit test project builds in Debug|x64
• Add test case to check that DescribeSchema works with VRT data sources
• Fix access violation in insert command discovered running unit tests under Debug|x64. The insert command was using "new OGRFeature" instead of OGRFeature::Create() which meant allocation and destruction was occurring on different heaps.

........
#937:

• Constant-ify "FID" and "GEOMETRY" special property names
• Add macros for:
  • Ensuring an open connection (ENSURE_OPEN_CONNECTION)
  • Throwing an equivalent FDO exception if CPLGetErrorMsg() is a non-empty string after certain GDAL/OGR API calls (CHECK_CPL_ERROR)
• Throw if we have non-empty strings from CPLGetErrorMsg() after enumerating OGR layers and in OgrFdoUtil::ConvertClass()
• Add extra VRT unit tests to test additional VRT data sources and to verify that exceptions are now thrown when describing the schema of various invalid VRT data sources.

........
#937: Include VrtTest in Makefile.am
........
#937: Ensure property names are tilde-fied by the OGR provider as dots in FDO property names are illegal. Add a test VRT that demonstrates the problem and unit tests to verify the fix.
........