Access SQL Server using Active Directory account

[MABrownParticipant]

We have PerfectForms on-premise with PerfectForms Agent connecting to a SQL Server database. Our current configuration uses SQL Server based accounts for database access, but we want to change to Active Directory accounts for management purposes.

We have created an AD account specifically for this purpose, granted it permissions on the server and in the database, and updated config.xml; but for some reason, PerfectForms Agent will not connect using this account. I repeatedly get the error, “Connection failed:Agent system error Login failed for user ‘<username>’.”

When I check the PerfectForms Agent Logs I see the error, “ERROR: Exception in GetColumnNames service: Login failed for user ‘<username>'”

When I check the SQL Server logs, I see “Login failed for user ‘<username>’.” and “Error: 18456, Severity: 14, State: 5.”

In the config.xml file, I have tried just the username, and combination of “<domain><username>”. (Note: when using “<domain><username>” the SQL Server logs show Error State: 6, instead of 5).

I don’t know if this is a SQL Server issue or a PerfectForms issue. Any ideas?

[ijoblingParticipant]

The ‘username’ that you define in the connection agent requires to be a SQL Authentication account, and from what you’ve said here (and also ‘googling’ the error: Error: 18456, Severity: 14, State: 5) this account may be ‘windows authentication’ and so does not have the required level of access.

[MABrownParticipant]

Does that mean the PerfectForms Agent does not support Windows Authentication (AD Accounts) for accessing SQL Server?

From what research I have done, it looks like PF Agent uses the jTDS JDBC driver to connect to SQL Server, and the documentation shows that jTDS does support Windows Authentication. I believe that it is necessary to populate a “Domain” parameter in the connection string (in addition to the username and password), but my knowledge and expertise is limited in this regard.

Can you provide any details about how PF Agent connects to SQL Server that might help me better understand the process?

[ijoblingParticipant]

At this time we do not support Windows Authentication in the Connection Agent, although it has been raised with the developers to look at an implement for a future release in 2010.

[Author]

Posts