SCCM Integration - SQL DB Windows Authentication

[jonfung]

Hello ServiceNow World,

Looking for a hand here. We are implementing SCCM Integration to the CMDB for our Desktop Enterprise and have hit a little roadblock.

Current Status is as follows:
-SCCM 3.0 Plugin - Check
-MID Server Configured and Online - Check

The question I have is around the SQL Database access required. The way our SCCM database is configured is for Windows Authentication only and not Mixed Mode which is apparently out of box for SCCM. We have AD credentials and access to the SCCM DB however our DBA has said that ServiceNow is attempting to authenticate via SQL authentication.

Can ServiceNow be configured to authenticate using Windows credentials or does it have to be SQL authentication?

See error below from connection test.

Data source: SCCM Disk
Error: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'xxxxxx'.
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source)
com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(Unknown Source)
com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
com.service_now.mid.connections.jdbc.JDBCConnection.connect(JDBCConnection.java:70)
com.service_now.mid.connections.jdbc.JDBCConnectionFactory.create(JDBCConnectionFactory.java:37)
com.service_now.mid.connections.ConnectionCachePool.getAvailableConnection(ConnectionCachePool.java:61)
com.service_now.mid.connections.ConnectionCache.get(ConnectionCache.java:88)
com.service_now.mid.probe.JDBCProbe.getJDBCConnection(JDBCProbe.java:613)
com.service_now.mid.probe.JDBCProbe.setConnectionStringFromDataSource(JDBCProbe.java:552)
com.service_now.mid.probe.JDBCProbe.init(JDBCProbe.java:480)
com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:83)
com.service_now.mid.probe.AProbe.process(AProbe.java:43)
com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:95)
com.service_now.mid.queue_worker.AManagedThread.run(AManagedThread.java:34)

com.service_now.mid.probe.JDBCProbe.getJDBCConnection(JDBCProbe.java:617)
com.service_now.mid.probe.JDBCProbe.setConnectionStringFromDataSource(JDBCProbe.java:552)
com.service_now.mid.probe.JDBCProbe.init(JDBCProbe.java:480)
com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:83)
com.service_now.mid.probe.AProbe.process(AProbe.java:43)
com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:95)
com.service_now.mid.queue_worker.AManagedThread.run(AManagedThread.java:34)

[robbika]

The answer is the following:

  - the MID Server service being used for SCCM must 'Run as' the MSSQL database user that can login to the database

          - find the service in Services on the Windows management console, right click and change the user it is running as, then restart

  - on your data sources, they must use Integrated authentication

        - notice that the username and password disappears on the form because you don't need them anymore.

Basically what is happening is that he MID Server is logging into SQL with the credentials it is running as.

That's it!

[coodey]

Hello Robert...

How is this handled with a mid server on a Linux box.

Thx!

[prdelong]

You have to use a local DB account to run the query. Integrated authentication can only be used with Windows MID Servers. Leave that unchecked and make sure you have the appropriate username/password combo along with the appropriate type of DB type/port/et al.

[chadcrawford]

Nice work Robert Scott. I had everything else right, but was missing the "Use integrated authentication" checkbox on the data sources.