Interview Questions: SQL Server Connectivity
Here we will discuss the important questions/points for a SQL Administrator
What is the main tool that can be used to perform the SQL Server network configuration changes
SQL Server Configuration Manager
What is the Main Role SQL Server Browser Service plays?
It helps the client who is trying to connect to the SQL Server instance to identify the list of SQL Server instances installed on that machine and the ports that these instances are listening to.
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? As a SQL Server database administrator Error message: System.InvalidOperationException: Timeout expired
The application code keeps opening connections without closing these connections. So, the code should be modified to close these connections.
What does the error message mean: Could not connect to server: A connection was successfully established to the server, but then an error occurred during the pre-login handshake.
If a native client is not compatible with TLS 1.2 and try connecting to it with driver such as OLE DB, ODBC and SQL Native client, this can also happen due to mismatch between protocols and cyphers configuration between application server and SQL Server
Minimum number of SPN records you need to register in the Active Directory for a named instance to fix a double-hop connection issue?
Two SPN records:
setspn -A MSSQLSvc/ServerName.Domain.com:instancename domain\accountname
setspn -A MSSQLSvc/ServerName..microsoft.com:1433 domain\accountnameConnecting to SQL Server and getting the error messages:
Login Failed for User ‘\’Check if the User is authorized to access the database, and check if the user connecting to database as the required permissions to perform the action
Can we configure the SQL server to listing to a dynamic TCP/IP port instead of using a Static TCP/IP port?
The Dynamic ports change automatically each time the server/instance is restarted, which forces to make changes to firewall rules and SQl Server Connectivity Scripts to update the new port, if a wide range of ports are opened to facilitate this, which is a big security risk for the server.
The error (2003) Can’t connect to MySQL server on server (10061)
The Error indicates that the network connection has been refused. Check whether your IP address is in firewall whitelist, you have entered correct MySQL Port number, Also you should check that there is a MySQL server running, that it has network connections enabled, and that the network port you specified is the one configured on the server.
Warning: mysqli::mysqli(): (HY000/2002): No such file or directory
Warning: mysqli::mysqli(): (HY000/2002): No such file or directory in /home/myhost/public_html/msql_connect.php on line 2 Connect Error (2002) No such file or directory
The above error occurs when the MySQL connection host is localhost this problem is reported as above.
Check the hosts, no problems:
127.0.0.1 localhost
::1 localhost localhost.localadmin localhost6 localhost.localdomain6
/etc/hosts
MySQL Status reports everything normal.
the cause of the problem is when the host is local host, MySQL will use UNIX domain socket for connection and when the host is 127.0.0.1, MySQL uses TCP/IP for connection, using Unix socket for connection is faster and more secure than TCP/IP.
Solution for the problem could be:
$myConnection – mysqli_connect(‘localhost’,’dbuname’,’dbpassword’,’mysql’);
Change it to
$myConnection – mysqli_connect(‘127.0.0.1′,’dbuname’,’dbpassword’,’mysql’);
Modify the MySQL configuration file my.cnf to specify the location of the mysql.socket: /var/lib/mysql/mysql.sock
$db – new MySQLi(‘localhost’,’dbuname’,’dbpassword’,’mysql_db’, ‘3306’, ‘/var/lib/mysql/mysql.sock’);
