Hi there,

Using ADDM v8.1, with TKUs all up to date to July 2010. Goal is to to discover Database names of our 170~ Microsoft SQL Server instances.

We had a local account created on one MS SQL instance with read-only access. Added the credential to the UI and it worked.

It seemed sensisble to see whether we could use our Active Directory account (“corp/svc-cc-tideway”) to connect to MS SQL. To check if it were possible, I manually logged into a server (different to the one above), opened MS SQL Management Studio, connected through Windows Authentication and ran the show databases query; this worked.

I then added “corp/svc-cc-tideway” to the MS SQL credential list on the ADDM UI, for the time being specifying it for just the IP address and port number of the server I manually logged into. Running a test (and rescans) gave the following error…

SQL Provider: Unable to open a connection to the database: Login failed for user ‘corp/svc-cc-tideway’.

…regardless of whether I left the password field blank or with the password entered on the credential (when I logged in using Windows Authentication on the server itself it just required I click ‘Connect’ rather than enter the user/password again).

Does this mean that the only way to connect to MS SQL Server instances is through local accounts on each instance or have I potentially not exhausted another way of doing this?

Just wanted to double-check as using one single username and password for this seems a more sensible approach to having 170 local ones created.

Thanks,
Josh

Yes this is possible, at least when creating a DB credential using the Microsoft SQL Server (jTDS Driver 1.2.2) driver.
I have not tested this with the Microsoft JDBC driver.

You should enter the username and password for the Windows user in the appropriate fields, and then in the ‘Additional JDBC parameters’ add: domain=<Windows domain>

Thanks Nikola – I’m now getting a differente error message when I test the credential (which I guess is progress!).

SQL Provider: Unable to open a connection to the database: Login failed for user “. The user is not associated with a trusted SQL Server connection.

Attached a screenshot of the settings I’m using too.

The account I’m using for the credential is the one which can log into all Windows servers as well as run queries if used to manually log into the server.

A different JDBC error. Can this user definitely access any database on the SQL Server in question?
Also, is the SQL Server set up to accept connections from remote hosts?
i.e. can you try to connect with an SQL Server client on a different host, using these domain credentials?

Definitely can.

When I log into the server with the account, and then MS SQL Management Studio I can manually run queries using the same account with no problem.

I also tried it with another account I know has read/write access in case it was a permissions problem and had the same error.

Was that positive answer to both of my questions?
Specifically, can you connect to the SQL Server using SQL Management Studio running on another host by using the same Windows domain credentials?

Yes it was – sorry should have clarified.

It is now working; I’m unsure why it would not connect yesterday afternoon / this morning but after scanning some hosts this afternoon the connection now works.

Thanks for the help and debugging this though – the forum is good for finding out about issues which might not necessarily be in the Configipedia pages.

That is good news.

It also reminds me that when I created the new credentials I had to re-submit them before it worked. Seems like something is cached somewhere for a little while.

Not sure if I should start a topic for this, especially as it is a bit of a tangent along the same lines.

Now this resolves the MS SQL instances, but for login to the Oracle DB Server SIDs is it possible to do something similar without the need for the creation of a login on each SID? (I’ll admit now I’m not really a very UNIX-aware person so it could be a stupid question to ask).

Actually MS SQL is different to all other databases. What is typically done is to ask the DBAs to create the same DB user on all the database servers you wish to interrogate. Then you can use the same credentials for all the Oracle DB servers you wish to connect to.

In my experience Oracle logins are local as they tend to be functional users. AD/Kerberos/LDAP style credentials tend to be handled by the applications sitting on top of Oracle that then uses an internal user to talk to the DB.

Josh Bates - 02 August 2010 08:09 PM

Thanks Nikola – I’m now getting a differente error message when I test the credential (which I guess is progress!).

SQL Provider: Unable to open a connection to the database: Login failed for user “. The user is not associated with a trusted SQL Server connection.

Attached a screenshot of the settings I’m using too.

The account I’m using for the credential is the one which can log into all Windows servers as well as run queries if used to manually log into the server.

I am running into the same exact issue. I have a domain account that can log into SQL Management studio and run queries. After reading this thread, I added the domain parameter. I still keep getting the same error mentioned in the quote above the user not being associated with a trusted SQL server.

Any ideas?

i have to discover SQL with Windows authentication. So i added the domain= command.
i still get an error

I presume you mean you added ‘domain=<domain name>’.

This is SQL complaining that you cannot authenticate as that user. So either the domain, user account or password is invalid.

Andrew Waters - 29 September 2010 08:03 AM

I presume you mean you added ‘domain=<domain name>’.

right, i mean under Additional JDBC Parameters set domain=<domain name>

OK, then i should test the user/pw i’m used for the connection

EDIT: But shouldn’t there be a Error that say invaid user and password?

Yes – test the user name / password combination. The username should not specify the domain.

Error messages are limited to what SQL Server reports. Unfortunately there is no sensible way to try and map all the messages that come back from database systems into standard ADDM messages.