In this article, we will discuss a number of questions that you may be asked while being interviewed for a SQL Server Database Administrator (DBA) position. These questions will concentrate on the SQL Server connectivity concept, components, configurations, and troubleshooting.
Note: To learn more about Performance tuning as a DBA skill, please read this article: Why SQL Performance Tuning is the Most Important Database Management Skill to Have
Q1: As a SQL Server database administrator, what is the main tool that you can use to perform the SQL Server network configuration changes?
The SQL Server Configuration Manager.
Q2: As a SQL Server database administrator, what do you think the main role that the SQL Server Browser Service plays?
The SQL Server Browser Service helps the client who is trying to connect to the SQL Server instance in identifying the list of SQL Server instances installed on that machine and the ports that these instances are listening on.
Q3: As a SQL Server database administrator, you are consulted to clarify why it is preferred to configure the SQL Server to listen on a static TCP/IP port instead of using a dynamic port?
When you configure the SQL Server to listen on dynamic ports, the port will be changed automatically each time the SQL Server instance restarted, enforcing you to change the firewall rules configuration continuously to allow connection from that new port or open a wide range of ports in the firewall configurations, which is risky from a security perspective.
Q4: You are a SQL Server database administrator in a company, and you are configuring the firewall rules on the Windows Server that is hosting your SQL Server. What are the ports that should be considered in the firewall rules, in order to allow connections to the following SQL Server components?
- SQL Server Engine default instance: (TCP port 1433)
- Dedicated Admin Connection: (TCP port 1434)
- SQL Server Browser Service: (UDP port 1434)
- Database Mirroring: (No default, but the commonly used is TCP port 5022)
- SQL Server Analysis Services: (TCP port 2383)
- Reporting Services Web Services: (TCP port 80)
- Microsoft Distributed Transaction Coordinator: (TCP port 135)
Q5: As a SQL Server database administrator, you are requested to prevent a client who is trying to search for a SQL Server instance, using the “Connect to Server” Browse button, from seeing that instance. How could you achieve that?
We can use the HideInstance flag under the Protocols for <server instance> under the SQL Server Configuration Manager. In this way, the SQL Server Browser service will not be able to expose the SQL Server instance to that client. But, to be able to connect to that instance, you should provide the port number that this SQL Server instance is listening on, in the connection string, even if the SQL Server Browser Service is running.
Take into consideration that, hiding the SQL Server instance may cause an issue when hiding a clustered instance, where the cluster service will not be able to reach that instance. This can be fixed by creating an alias on each node that shows the other nodes names and the port numbers that these nodes are listening on.
Q6: You are working as a SQL Server database administrator in a company. A client complains that he is trying to connect to a specific SQL Server instance but getting the below error message. What are the steps that you should follow in order to troubleshoot that connectivity issue?
- A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (Provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)
Ask first the “one million” questions. Was it working before?
If it was not working before, review the overall network configurations of that new installation. If it was working before, follow the steps below:
- Check that the SQL Server instance that the client is trying to connect to is online, the TCP/ IP protocol is enabled and that you are able to connect to it locally from the hosting server with no issue
- Make sure that the name of the SQL Server instance used in the connection string is correct
- Check if there is any incorrect alias configured in the machine from where you are trying to connect. This can be checked from the SQL Server Configuration Manager tool
- Try to ping the name of that SQL Server machine. If the ping is not working, check with the network team to review the connection between the servers
- Try to telnet the SQL Server instance using the IP address of the SQL Server with the TCP port that the instance is listening on. If telnet is not working check the firewall configurations on the SQL Server to make sure that it allows connections from the client machine on the configured TCP port
- If it is a named instance, make sure that the SQL Server Browser Service is running and that the UDP port 1434 is opened in the SQL Server firewall
Q7: You are a SQL Server database administrator in a company. One of your clients complains that, while trying to connect to a SQL Server instance he got the error message below. How could you troubleshoot that issue?
- The target principal name is incorrect. Cannot generate SSPI context.
Most of the time, the “Cannot generate SSPI context” error is faced when trying to connect to the SQL Server instance using Windows Authentication method, where the Security Support Provider Interface (SSPI) uses the Kerberos authentication method to delegate the authentication over TCP/IP protocol, but this delegation cannot be completed successfully, due to many reasons, such as missing, misplaced or duplicate SPN records.
To confirm that the issue is related to the Kerberos authentication, try to connect using a SQL Authentication account, or connect using the IP address of the SQL Server instance instead of the instance name. If the connection works then the issue is related to Kerberos Authentication.
Check if the SQL Server service account is changed recently from a built-in account to a local or domain account. This is because the Built-in accounts have permission to register the SPNs automatically with the Active Directory, but the local accounts and the domain accounts do not have default permission on the Active Directory to register SPNs. As a result, the SPNs registration attempts will fail. To fix that issue, you need to register the SPNs manually.
Use the Microsoft Kerberos Configuration Manager to check for any missing, misplaced or duplicate SPN record and fix it based on the tool recommendations.
You can also use the SETSPN CMD command to check if the SPNs are registered correctly and under the correct service account.
If the SPN records are set correctly and you still face the issue, make sure that the server name is resolved correctly using the “Ping -a” command.
Note: To learn more about trends that impact Database Administration, please read this article: 5 DBMS Trends Impacting Database Administration
Q8: You are a SQL Server database administrator in a company. One of your clients complains that he is not able to connect to the SQL Server and getting the error message below. How could you troubleshoot it?
- Login Failed for User ‘<domainname>\<username>’
- Check that the database is accessible by another authorized user
- Check that the user who is trying to connect has permission to perform that action to the database
Q9: You are a SQL Server database administrator in a company. One of your clients complains that his query that is connecting to a remote SQL Server using a linked server failed and getting the error message below. How could you troubleshoot it?
- Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’
This is a failed double-hop scenario, where you are trying to perform a double-hop connection, but it failed due to using the NTLM credentials instead of Kerberos authentication.
This can be fixed by checking the missing, misplaced or double SPN records using the SETSPN CMD command or the Microsoft Kerberos Configuration Manager tool, as described in Q7.
Q10: You are a SQL Server database administrator in a company. One of your clients complains that his application is receiving the below error message frequently. How could you troubleshoot it?
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
This error is an indication of two types of timeout issues and needs to get more details about the complete error message from the application logs, the SQL Server error logs and the event logs.
If the complete error message shows a Connection timeout issue, we need to make sure that the SQL Server instance is reachable as in Q6 and that there is no network issue between the application and the SQL Servers. We can also increase the connection timeout value from the application side.
If the error indicates a Command Timeout issue, tune the performance of the query that you are using to retrieve the data from the SQL Server then increase the Command Timeout value from the application side.
Q11: You are working as a SQL Server database administrator in a company. A client complains that he is trying to connect to a specific SQL Server instance but getting the below error message. What are the steps that you should follow in order to troubleshoot that connectivity issue?
- System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.
You may face this issue when all SQL Server pooled connections are in use and the configured value of the max pool size already reached.
This issue can be fixed from the application side, where the application code keeps opening connections without closing these connections. So, the code should be modified to close these connections.
Q12: You are working as a SQL Server database administrator in a company. A client complains that he is trying to connect to a specific SQL Server instance but getting the below error message. What are the steps that you should follow in order to troubleshoot that connectivity issue?
- Could not connect to server: A connection was successfully established to the server, but then an error occurred during the pre-login handshake.
This error is received when trying to connect to a SQL Server instance using a driver, such as OLE DB, ODBC, and SQL Native Client that is not compatible with TLS 1.2.
This can be confirmed by performing a UDL test using another driver that is compatible with the TLS 1.2 protocol. Check the TLS 1.2 support for SQL Server Support.
This issue can be also caused due to mismatch between the protocols and cyphers configurations between the application server and the SQL Server. This can be checked and tuned using the IISCrypto tool.
Q13: You are working as a SQL Server database administrator in a company. What does the UDL file mean for you?
The UDL file is a connectivity check tool that can be used to test the connection to a local or remote database engine using different types of connection providers that are installed on that machine.
It requires no installation. Just create an empty .txt file and rename it to .UDL. Then use it easily to test the connection using the selected driver.
Q14: You are working as a SQL Server database administrator in a company. What is the minimum number of SPN records you need to register in the Active Directory for a named instance to fix a double-hop connection issue?
You should register two SPN records, as below:
- setspn -A MSSQLSvc/ServerName.Domain.com:instancename domain\accountname
- setspn -A MSSQLSvc/ServerName..microsoft.com:1433 domain\accountname
Q15: You are working as a SQL Server database administrator in a company. You just finished configuring the Always-on read-only routing list in the Always-on Availability Group site. How could you test the connection to the secondary read-only replica using SSMS?
From the Connect to Server window -> Options -> Additional Connection Parameters -> write (ApplicationIntent=ReadOnly) then provide the name of the secondary replica to test the connection.
Conclusion
In this article, we discussed a number of important questions, related to the SQL Server Connectivity subject, that you may be asked when attending a SQL Server Database Administrator (DBA) job interview. I wish you all the best in your interviews.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021