Data360 Analyze

Hi Team,

Above is the SQL error message log that is created when I am trying to connect my on-prem analyze server to a SQL server.

I am using the Username and password attributes in the MetaData Connector as the SQL server will need to ensure I am the correct user, however the error message suggests that my service account (GDC\Data360Service) is trying to use SQL auth and not Windows (NT) auth.

I am obviously getting a login error in analyze too. 

Is there a way to tweak the connection or some flag somewhere that will allow me to connect using windows Auth?

Many Thanks

John

Authentication via Windows / LDAP (non-SQL Server) accounts is controlled by the integratedSecurity flag within the JDBC connection URL. This setting is disabled by default, which falls back to standard SQL Server authentication. 

Windows authentication can be enabled by appending this string to end your database connection URL:

;integratedSecurity=true

For example:

jdbc:sqlserver://samplehost\DbInstance;Database=DbName;integratedSecurity=true

Depending on your Data360 Analyze version, you may need to add an extra DLL (sqljdbc_auth.dll) that allows the driver to use Windows authentication. You'll know if you need the DLL or not if you add integratedSecurity, run the node, and get an error saying that the database driver doesn't support Windows authentication. If you get this error, the instructions to download and add sqljdbc_auth.dll can be found here: How to enable SQL Server Integrated Security.

Ok great,

The next challenge in that case would be the username and password I would be using. I assume from the above that the username and password are not passed over when using integrated Security as the SQL will look to the windows credential of the machine trying to connect in order to authorise the connection.

In my scenario I need to be able to connect to multiple domains within order network so I would need to be able to pass over the domain switch in my username. How could I achieve this using windows auth on a single analyze instance?

What are my options here?

Thanks

John

With the integrated security in use, the authentication comes from the user who owns the Analyze services. To go on a slight tangent, this is the Local System user by default but can be updated to any user within the Analyze Services' Log On settings followed by restarting the Analyze Services:

To your point though, this would be a single user across the Analyze installation. In other Analyze implementations using a similar setup, a service account is created and granted access to the various databases, and then that service account is set as the Analyze services' Log On user shown above.

Many thanks for your response, as I thought the service / Server would need to hold the correct credentials to each SQL server I want to connect to, when the domains change this is impossible.

I think the way forward is to use direct SQL authentication. This does however raise its own security concerns and these direct assignment may not be part of standard AD authentication monitoring.

Direct SQL may be the short term fix and then we could always roll out the 3 separate server to run the login from different domains later on.

Thanks

John

I should have specified in my last response: the service account added would be an LDAP-based service account. With a single LDAP-based account, you can set that as your owner of the Analyze services, which is then used for any database connections where integrated security is enabled.

Then within your each of the databases you want to connect to, permissions would need to be added for that LDAP service account. Adding more databases would be as simple as adding permissions within that new database for the existing service account rather than rolling out additional Analyze instances.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

If your Windows Services are not running as the Windows User that you want to authenticate against the SQL Server with, you can also enter the username and password in the DbUser and DbPassword fields. In your case the DbUser is Data360Service and GDC is the domain value to be put in the DbUrl. Your DbUrl would look something like this:

jdbc:sqlserver://<hostname>:<port>;authenticationScheme=NTLM;domain=<DOMAIN>;integratedSecurity=true

In this scenario it does not matter what your Windows Services are running as and you can change the DbUser, DbPassword and DbUrl as needed for your connection.

This sounds perfect!

I need to have the ability to authenticate user 3 different service account credentials for the 3 different domains. like so:

1. Domain 1 - GDC; SA= XXX; Password=111
2. Domain 2 - ABC; SA= YYY; Password=555
3. Domain 3 - HFHF; SA= ZZZ; Password=999

It seems that using NTLM is a great solution to this scenario.

If my understanding is correct, I will be able to use a single Analyze application to connection to various domains, using defined usernames and password that I set when creating the connection.

Many thanks for your help. I will pass on this suggestion to my Db team and see how we progress

<x-zendesk-user data-user-name="Gerry Mullin">374183093373</x-zendesk-user>  - Could we set up a quick call to walk this through? - I believe the DB guys my side have done what is needed for the first connection and I would like to test the connection is all working before we push out any further

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

I'll get a support ticket open and reach out to you.