SQL Server连接面试SQL Server数据库管理员问答

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.

在本文中，我们将讨论在面试SQL Server数据库管理员（DBA）职位时可能会问到的许多问题。 这些问题将集中在SQL Server连接概念，组件，配置和疑难解答上。

• 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

  注意 ：要了解有关作为DBA技能进行性能调优的更多信息，请阅读本文： 为什么SQL性能调优是最重要的数据库管理技能

Q1: As a SQL Server database administrator, what is the main tool that you can use to perform the SQL Server network configuration changes?

Q1：作为SQL Server数据库管理员，可用于执行SQL Server网络配置更改的主要工具是什么？

The SQL Server Configuration Manager.

SQL Server配置管理器。

Q2: As a SQL Server database administrator, what do you think the main role that the SQL Server Browser Service plays?

问题2：作为SQL Server数据库管理员，您认为SQL Server浏览器服务扮演的主要角色是什么？

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.

SQL Server浏览器服务可帮助尝试连接到SQL Server实例的客户端识别安装在该计算机上SQL Server实例列表以及这些实例正在侦听的端口。

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?

问题3：作为SQL Server数据库管理员，您需要咨询您以澄清为什么首选将SQL Server配置为在静态TCP / IP端口上侦听而不是使用动态端口吗？

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.

将SQL Server配置为侦听动态端口时，每次重新启动SQL Server实例时，该端口都会自动更改，从而强制您不断更改防火墙规则配置，以允许从该新端口进行连接或在其中打开各种端口。防火墙配置，从安全角度来看这是有风险的。

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?

问题4：您是一家公司中SQL Server数据库管理员，并且正在承载SQL Server的Windows Server上配置防火墙规则。 为了允许连接到以下SQL Server组件，防火墙规则中应考虑哪些端口？

• SQL Server Engine default instance: (TCP port 1433)
  SQL Server Engine默认实例：（TCP端口1433）
• Dedicated Admin Connection: (TCP port 1434)
  专用管理员连接：（TCP端口1434）
• SQL Server Browser Service: (UDP port 1434)
  SQL Server浏览器服务：（UDP端口1434）
• Database Mirroring: (No default, but the commonly used is TCP port 5022)
  数据库镜像：（无默认值，但常用的是TCP端口5022）
• SQL Server Analysis Services: (TCP port 2383)
  SQL Server Analysis Services：（TCP端口2383）
• Reporting Services Web Services: (TCP port 80)
  Reporting Services Web服务：（TCP端口80）
• Microsoft Distributed Transaction Coordinator: (TCP port 135)
  Microsoft分布式事务处理协调器：（TCP端口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?

问题5：作为SQL Server数据库管理员，要求您阻止试图使用“连接到服务器”浏览按钮搜索SQL Server实例的客户端看到该实例。 你怎么能做到？

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.

我们可以在SQL Server配置管理器的<服务器实例>的协议下使用HideInstance标志。 这样，SQL Server浏览器服务将无法向该客户端公开SQL Server实例。 但是，为了能够连接到该实例，即使SQL Server Browser Service正在运行，也应在连接字符串中提供此SQL Server实例正在侦听的端口号。

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.

请考虑到，隐藏SQL Server实例可能会导致隐藏群集实例时出现问题，而群集服务将无法访问该实例。 可以通过在每个节点上创建一个别名来解决此问题，该别名显示其他节点名称和这些节点正在侦听的端口号。

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?

问题6：您正在公司中担任SQL Server数据库管理员。 客户端抱怨他试图连接到特定SQL Server实例，但收到以下错误消息。 要解决该连接问题，应遵循哪些步骤？

• 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)
• 建立与SQL Server的连接时发生与网络相关或特定于实例的错误。 服务器未找到或无法访问。 验证实例名称正确，并且已将SQL Server配置为允许远程连接。 （提供者：SQL网络接口，错误：26 –指定服务器/实例时出错）

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
  检查客户端尝试连接SQL Server实例是否在线，是否启用了TCP / IP协议以及是否可以从托管服务器本地连接到该实例
• Make sure that the name of the SQL Server instance used in the connection string is correct
  确保连接字符串中使用SQL Server实例的名称正确
• 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
  检查您尝试连接的机器中是否配置了任何不正确的别名。 可以从SQL Server配置管理器工具中检查
• 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
  尝试ping该SQL Server计算机的名称。 如果ping不起作用，请与网络团队联系以检查服务器之间的连接
• 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
  尝试使用SQL Server的IP地址和实例正在侦听的TCP端口通过telnet SQL Server实例。 如果telnet无法正常工作，请检查SQL Server上的防火墙配置，以确保它允许来自客户端计算机的已配置TCP端口上的连接
• 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
  如果它是命名实例，请确保SQL Server Browser服务正在运行，并且在SQL Server防火墙中打开了UDP端口1434。

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?

问题7：您是公司中SQL Server数据库管理员。 您的一位客户抱怨说，在尝试连接到SQL Server实例时，他收到以下错误消息。 您如何解决该问题？

• The target principal name is incorrect. Cannot generate SSPI context.
• 目标主体名称不正确。 无法生成SSPI上下文。

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.

在大多数情况下，尝试使用Windows身份验证方法连接到SQL Server实例时会遇到“无法生成SSPI上下文”错误，其中安全支持提供程序接口（SSPI）使用Kerberos身份验证方法来委派TCP / IP协议，但是由于许多原因（例如丢失，放错位置或重复的SPN记录），无法成功完成此委派。

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.

若要确认该问题与Kerberos身份验证有关，请尝试使用SQL身份验证帐户进行连接，或者尝试使用SQL Server实例的IP地址而不是实例名称进行连接。 如果连接正常，则问题与Kerberos身份验证有关。

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.

检查最近是否将SQL Server服务帐户从内置帐户更改为本地帐户或域帐户。 这是因为内置帐户具有在Active Directory中自动注册SPN的权限，但是本地帐户和域帐户在Active Directory中没有默认的权限来注册SPN。 结果，SPN的注册尝试将失败。 要解决此问题，您需要手动注册SPN。

Use the Microsoft Kerberos Configuration Manager to check for any missing, misplaced or duplicate SPN record and fix it based on the tool recommendations.

使用Microsoft Kerberos配置管理器检查是否有丢失，放错位置或重复的SPN记录，并根据工具建议对其进行修复。

You can also use the SETSPN CMD command to check if the SPNs are registered correctly and under the correct service account.

您还可以使用SETSPN CMD命令来检查SPN是否已正确注册并且在正确的服务帐户下。

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.

如果正确设置了SPN记录，但仍然遇到问题，请使用“ Ping -a”命令确保正确解析了服务器名称。

• Note: To learn more about trends that impact Database Administration, please read this article: 5 DBMS Trends Impacting Database Administration

  注意 ：要了解有关影响数据库管理的趋势的更多信息，请阅读本文： 5影响数据库管理的DBMS趋势

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?

Q8：您是公司中SQL Server数据库管理员。 您的一位客户抱怨说，他无法连接到SQL Server，并收到以下错误消息。 您如何解决它？

• 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?

问题9：您是公司中SQL Server数据库管理员。 您的一位客户抱怨说，他使用链接服务器连接到远程SQL Server的查询失败，并在下面显示错误消息。 您如何解决它？

• Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’用户“ 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.

这是一个失败的双跳方案，您尝试执行双跳连接，但是由于使用NTLM凭据而不是Kerberos身份验证而失败。

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.

可以通过使用SETSPN CMD命令或Microsoft Kerberos Configuration Manager工具检查丢失，放错位置或重复的SPN记录来解决此问题，如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?

Q10：您是公司中SQL Server数据库管理员。 您的一位客户抱怨他的应用程序经常收到以下错误消息。 您如何解决它？

• 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.

此错误表示两种类型的超时问题，需要从应用程序日志，SQL Server错误日志和事件日志中获取有关完整错误消息的更多详细信息。

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.

如果完整的错误消息显示连接超时问题，则我们需要确保SQL Server实例与Q6中一样可访问，并且应用程序和SQL Server之间没有网络问题。 我们还可以从应用程序端增加连接超时值。

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.

如果错误指示命令超时问题，请调整用于从SQL Server检索数据的查询的性能，然后从应用程序端增加命令超时值。

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?

问题11：您正在公司中担任SQL Server数据库管理员。 客户端抱怨他试图连接到特定SQL Server实例，但收到以下错误消息。 要解决该连接问题，应遵循哪些步骤？

• System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.
• System.InvalidOperationException：超时已过期。 从池中获取连接之前已经过超时时间。

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.

当所有SQL Server池连接都在使用中并且已达到最大池大小的配置值时，您可能会遇到此问题。

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?

问题12：您正在公司中担任SQL Server数据库管理员。 客户端抱怨他试图连接到特定SQL Server实例，但收到以下错误消息。 要解决该连接问题，应遵循哪些步骤？

• 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.

尝试使用与TLS 1.2不兼容的驱动程序（例如OLE DB，ODBC和SQL Native Client）连接到SQL Server实例时，会收到此错误。

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.

可以通过使用与TLS 1.2协议兼容的另一个驱动程序执行UDL测试来确认这一点。 检查TLS 1.2对SQL Server支持的支持 。

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.

由于应用程序服务器和SQL Server之间的协议和密码配置不匹配，也可能导致此问题。 可以使用IISCrypto工具检查和调整。

Q13: You are working as a SQL Server database administrator in a company. What does the UDL file mean for you?

问题13：您正在公司中担任SQL Server数据库管理员。 UDL文件对您意味着什么？

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.

UDL文件是一种连接检查工具，可用于使用该计算机上安装的不同类型的连接提供程序来测试与本地或远程数据库引擎的连接。

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.

它不需要安装。 只需创建一个空的.txt文件并将其重命名为.UDL。 然后，使用所选驱动程序轻松使用它来测试连接。

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?

问题14：您正在公司中担任SQL Server数据库管理员。 您需要在Active Directory中为命名实例注册的SPN记录的最小数量是多少，以解决双跳连接问题？

You should register two SPN records, as below:

您应该注册两个SPN记录，如下所示：

• setspn -A MSSQLSvc/ServerName.Domain.com:instancename domain\accountname
  setspn -A MSSQLSvc / ServerName.Domain.com：instancename域\帐户名
• setspn -A MSSQLSvc/ServerName..microsoft.com:1433 domain\accountname
  setspn -A MSSQLSvc / ServerName..microsoft.com：1433域\帐户名

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?

问题15：您正在公司中担任SQL Server数据库管理员。 您刚刚完成了Always-on可用性组站点中的Always-on只读路由列表的配置。 您如何使用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.

从“连接到服务器”窗口->选项->其他连接参数->写入（ApplicationIntent = ReadOnly），然后提供辅助副本的名称以测试连接。

结论 (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.

在本文中，我们讨论了许多与SQL Server连接性主题相关的重要问题，在参加SQL Server数据库管理员（DBA）求职面试时可能会询问您。 祝您在面试中一切顺利。

翻译自: https://www.sqlshack.com/sql-server-connectivity-interview-questions-answers-for-sql-server-database-administrators/