If the UDL connection refused and prompt an error as shown below
We have to register SQL with the account name: “<domain>\user” with the help of SPN (Service Principal Name) tool. The SPN is a unique idendifier for each service that is running on servers. It is essentially a mapping between a principal name and the Windows account that started the server instance service. This is needed because the client will use the server’s hostname and the TCP/IP port to which it connects to compose an SPN. If the SPN mapping has not been performed, then the Windows security layer will be unable to determine the account associated with the SPN. To register the SPN syntax is;
setspn –A MSSQLSvc/<servername>.<domainname>:<port> accountname
Open a command window with run as administrator and write as shown below and click “Enter”.
The result must be “Updated object” as shown below
Re-test UDL connection.
If you get an error; “Insufficient access rights to perform the operation.” As shown below, please follow the steps on DC server.
Go to DC Server or contact your system administrator if you have no access to it to give “Read servicePrincipalName” and “Write servicePrincipleName” permission to installuser as described below steps
Click Start, click Run, type Adsiedit.msc, and then click OK
In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName , and then click Properties.
Notes
– DomainName is a placeholder for the name of the domain.
– RootDomainName is a placeholder for the name of the root domain.
– AccountName is a placeholder for the account that you specify to start the SQL Server service.
– If you specify the Local System account to start the SQL Server service, AccountName is a placeholder for the account that you use to log on to Microsoft Windows.
– If you specify a domain user account to start the SQL Server service, AccountName is a placeholder for the domain user account.
In the CN= AccountName Properties dialog box, click the Security tab.
On the Security tab, click Advanced.
In the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries. (If SELF is not listed, click Add, and then add SELF.)
Under Permission entries, click SELF, and then click Edit.
In the Permission Entry dialog box, click the Properties tab.
On the Properties tab, click This object only in the Apply onto list, and then click to select the check boxes for the following permissions under Permissions:
– Read servicePrincipalName
– Write servicePrincipalName
Click OK three times.
Close ADSI edit and go to your server where UDL has been created.
Re-test UDL connection