Imports from Microsoft SQL Server via MID Server is very popular, but since this is an integration covering 2 endpoints (ServiceNow MID Server and MS SQL Server), it might be difficult to understand where the issue resides in case of an import failure.
This article will describe how you can validate the MS SQL Server connectivity from the MID Server host on Windows OS without the use of ServiceNow platform. After following the steps described here, and if you have a successful connection, you will make sure that the SQL Server connection from the MID Server host is intact.
Windows Integrated Authentication option on ServiceNow data source is indicated with the flag "Use integrated authentication", but you should engage your SQL Server administrator to learn about the actual configuration on installed SQL Server.
If you have confirmed that you're not using integrated authentication, you can login with any valid Windows user account.
2. Make sure you have enabled viewing file name extensions on File Explorer:
3. Create a new text file on your desktop by right clicking on an empty area and navigating to "New" > "Text Document":
4. Rename the file to have .udl extension:
Windows will give a warning that the file might be unusable, but proceed with Yes:
File icon will change to a Data Link definition:
5. Double click on this file, and open the Data Link Properties dialog box. Populate the DB connection information that you have specified on the ServiceNow Data Source record.
If your SQL Server is enforcing Windows Authentication and you have logged into the MID Server host with the Windows credentials of the MID server process as mentioned in the 1st point, then you should select "Use Windows NT Integrated security":
If your SQL Server is in Mixed Mode for authentication and you have specified a SQL Server account on ServiceNow datasource, then you should select "Use a specific user name and password":
6. Click on Test Connection. If the connection successful, you should observe "Test connection succeeded.":
This test concludes that your database connection information and credential validity on Microsoft SQL Server end.