Error while connecting to MS SQL database using windows authentication

Archives Ansible Developer
1

Hi,

I am building a Jenkins pipeline to deploy SQL artifact to Microsoft SQL server(2012) using Liquibase. However it’s giving error while connecting to the database. I am using windows authentication credential to connect to the database.

I have validated/executed below steps so far-

  1. WINRM has been enabled to the server.

  2. Using Kerberos Authentication to connect to the database(Earlier enabled CREDSSP but found out that need to connect using Kerberos authentication method). Verified using below query-

select auth_scheme from sys.dm_exec_connections where session_id=@@spid



3. Registered SPN for service account for Kerbros authentication using below command-




*setspn -s MSSQLSvc/FQDN <domain>\<Service account>*

4. Below connection string is getting used to connect to the database in the Ansible playbook*-*

*url: jdbc:{{ db_conn }}{{ hostname }}:{{ db_port}};database_name={{ dbname}};integratedSecurity=true;authenticationScheme=JavaKerberos * 

*username: {{ base_username }}*

*password: {{ base_password }}*

where base_username is a variable which contains username in "username@MY.DOMAIN.COM" format.

base_password is a variable which contains Vault password.

5. Playbook Site.yml format-

- name: Template SQL Scripts into XML Format for Liquibase

  gather_facts: true

  hosts: localhost

  roles:

    - { role: generating_properties, tags:["properties_generating"]

    - { role: maven_liquibase, tags:["install_maven"] }

environment:

   LIBRARY_PATH: "{{ LIBRARY_PATH}}"

6. logged into the database manually using this AD user and it's accessing the database successfully.

Note- It's connecting successfully to the database when using SQL authenticated credential.

Can anyone help in troubleshooting this issue?