Recently i faced the issue while creating a DSN to connect to SQL Server from remote machine. I am getting login failure message and below is the error logged in error log file.
Error: 18456, Severity: 14, State: 12
Login failed for user 'admin'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors
1. No issue with login name and password as i am able to connect to SQL Server using SSMS.
2. If we specify sysadmin to the failed login then DSN is creating without any issues.
I have executed the below commands to fix the issue in my environment,
GRANT CONNECT SQL TO "admin"
GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO "admin"
As a quick resolution you can give sysadmin role to user and create dsn and remove the sysadmin role.
No comments: