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)

[jpro]

Yes, the service account that instaintiated the mid server being used for the SCCM integration needs to have read access to the SCCM database/tables.

Jim

[jduchock1]

I have the same problem here, though i can use SQL Enterprise Studio to connect and verify that my username is appropriate but i still get the same error as above.

Using a designated username:

Error: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'USERNAME'.

If i check the box for "Use integrated authentication" i get:

Error: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'COMPUTERNAME$'.

I am not sure why checking the integrated authentication box will force authentication using the computer name, though it obviously will not work.

Need help !

[goswami_sudipta]

Hello,

Not sure if your problem got fixed, but I tried with following details and it worked out for me

1. My mid server is setup locally with my SNow UserID and Password
2. My SQL 2008 DB is also setup locally and allow mix mode authentication
3. No Logins are there in my SQL 2008 Instance/DB for my SNow User
4. Used Integrated Authentication mode in SNow JDBC Data Source to fetch data from SQL Server DB

However, I would like to see how a JDBC data source works when either Mid server or the SQL server are located remotely. Or in case anyone tried/experienced the mentioned scenario, your findings/sharing would be most appreciated!

Thank you!

[rrodriguez]

I am also running my SCCM 2012 database server in Windows-only authentication due to our security policies.   Initially the integration failed with the same errors the OP showed.   The suggestion to change each of the Data Sources that is created by the integration to use Integrated Authentication worked for me, but the difference may be that I am running the Windows Service on the MID Server as the same account that has read rights to the SCCM tables.

So I think what is happening is the SN instance is giving the query to the MID server and telling it to use its own credentials (those of the running service) to access the network resource.

Richie

[prashu]

Hi Goswami,

Can you please explain me the steps:

how to install/setup Sql server and the mid server   locally.

Thanks in advance,

Prashanthi