Highlights of SQL Server Problems

Check whether the server IP can be pinged.
This is to see whether the physical connection to the remote SQL Server 2000 Server exists. If not, check the network and check the configuration. Make sure that the IP address of the Remote SQL Server 2000 Server is correctly spelled.
2. Enter the IP port of the Telnet server in DOS or command line to check whether the port can be connected.
For example, Telnet 202.114.100.100 1433
The port value is usually 1433, because 1433 is the default listening port of SQL Server 2000 for TCP/IP. If there is a problem, this step usually has a problem. The common prompt is "... Unable to open the connection, connection failed ". If there is a problem with this step, check the following options.
1. Check whether the remote server has started the SQL Server 2000 Service. If not, start.
2. Check whether the TCP/IP protocol is enabled on the server. This Protocol is required for remote connection (over the Internet. The check method is to open the Start menu on the server-> Program -> Microsoft SQL Server-> server network utility to check whether the enabled Protocol has the TCP/IP protocol. If not, enable it.
3. Check whether the server's TCP/IP Port is configured as port 1433. Check the TCP/IP attribute in the enabled protocol in the server network utility to make sure that the default port is 1433 and the check box of the hidden server is not checked.
In fact, it is acceptable if the default port is modified. However, when the client performs a telnet test, the server port number must be consistent with the port number configured on the server. If the "Hide server" check box is selected, it means that the client cannot see this server by enumerating the server, which protects the server but does not affect the connection, however, the default port of TCP/IP protocol is implicitly modified to 2433, which must be changed when the client is connected.
4. If the operating system on the server has been patched with SP2, you must configure Windows Firewall and open port 1433 to it, you can usually turn off the Windows Firewall directly during testing (Other firewalls are also the best ).
5. Check whether the server listens on port 1433. If the server does not listen on port 1433 of the TCP connection, it cannot be connected. The check method is to input
Netstat-a-n or netstat-An. check whether there are items similar to TCP 127.0.0.1 1433 listening in the result list. If not, you usually need to patch SQL Server 2000 with at least SP3. In fact, start the query analyzer on the server and enter select @ version to execute it. The version number must be patched for versions earlier than 8.0.2039.
If there is no problem above, then you can perform the telnet server IP 1433 test. The screen will flash and the cursor will not stop flashing in the upper left corner. Congratulations, you can start to connect to the Enterprise Manager or query analyzer immediately.
3. Check client settings
Program> Microsoft SQL Server> client network tools. Make sure that the client TCP/IP protocol is enabled, as in the server network utility, and the default port is 1433 (or other ports, consistent with the server ).
4. In the Enterprise Manager or query the analyzer connection test
Enterprise Manager-> right-click sqlserver group-> Create sqlserver registration-> next-> Write Remote IP-> next-> select sqlserver login-> next-> write login name and password (SA, password)-> next-> finish
Query analyzer-> file-> connection-> Write Remote IP-> write login name and password (SA, password)-> OK
It is usually recommended to do this in the query analyzer, because by default, the timeout setting for registering another SQL server through the Enterprise Manager is 4 seconds, and the query analyzer is 15 seconds.
Modify the default connection timeout method:
Enterprise Manager-> Tools-> options-> In the displayed "SQL Server Enterprise Manager properties" window, click the "advanced" tab-> connection settings-> logon timeout (seconds) enter a large number in the following box.
Query analyzer-> tool-> Option-> connection-> enter a large number in the box after logon timeout (seconds). If an error is prompted, then go to the next step.
Five errors are usually caused by SQL Server's "Windows only" authentication method. Therefore, you cannot connect to SQL Server's Logon account (such as SA. The solution is as follows:
1. Use the enterprise manager on the server side and select "use Windows Authentication" to connect to SQL Server.
2. Expand "SQL Server group", right-click the name of the SQL Server server, select "properties", and then select the "Security" tab.
3. Under "authentication", select "SQL Server and Windows ".
4. Restart the SQL Server service. (In DoS or command line, net stop MSSQLServer to stop the service, and net start MSSQLServer to start the service, which is also a quick method ).
Note: When connecting to the local server, the named pipe protocol is usually used (this is enabled in the server network utility). The default port is 445, therefore, local connectivity cannot be explained. connecting to a remote server is a completely different protocol)

SQL Server 2000 cannot be remotely connected
This article focuses on SQL Server 2000 installation on Windows 2003.
In Windows 2003, if SQL2000 is not patched, SQL Server 2000 does not listen to port 1433. In this case, you can connect to the database on the local machine, but cannot connect from other machines. After the SP4 patch is applied, you can connect to the instance remotely. Sometimes, after the SP4 patch is applied, the default listening port of SQL Server 2000 is 1433 instead of 2433.
Therefore, after installing SQL Server 2000, install the SP4 patch first. If the connection fails after the patch is completed, use netstate-An to check whether port 1433 is listened on. If the listener is 2433, you can set the SQL server port on the client or use the Enterprise Manager, right-click the server and select Properties> network configuration> TCP/IP on the right of the node to modify the port listened by SQL Server.
If the port is normal, check whether port 1433 is enabled in the firewall of win2003.

How can I solve the problem that I cannot install SQL server again after I uninstall it?
You only need to delete the pendingfilerenameoperations key value under HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Control \ Session Manager \, and then reinstall it.
HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Services \ The following services are existing in the system, delete the corresponding items

SQL Server Security Settings
1. confirm that the latest patches for NT/2000 and SQL server have been installed. Needless to say, you should have installed them, but I think it is best to remind you here.
2. evaluate and select a network protocol that considers maximum security but does not affect functionality. Multi-Protocol is a wise choice, but it is sometimes not used in a heterogeneous environment.
3. Set strong passwords for "sa" and "probe" accounts to enhance their security. Set a strong password and store it in a safe place. Note: The probe account is used for performance analysis and distribution transmission. When used in standard security mode, setting a strong password for this account can affect the use of certain functions.
4. Use a low-privilege user as the account for querying SQL Server services. Do not use LocalSystem or SA. This account should have the minimum right (note that the right to run as a service is required) and should contain (but do not stop) Attacks on the server in case of compromise. Note that when you use the Enterprise Manager for the preceding settings, the files, registries, and ACLs on user rights are processed at the same time.
5. determine all SQL server data, and the system file is mounted to the NTFS partition, and appropraite ACLs is applied. If someone gets access to the system, this level of permission can prevent intruders from damaging data and avoid causing a disaster.
6. If xp_cmdshell is not used, disable it. If SQL 6.5 is used, at least use sqlexecutiecmdexec account in server options to restrict non-sa users from using xp_javasshell.

 

If you do not need to extend the Stored Procedure xp_mongoshell, remove it. Use this SQL statement:
Use master
Sp_dropextendedproc 'xp _ export shell'
Xp_mongoshell is the best way to enter the operating system and a large backdoor left by the database to the operating system. If you need this stored procedure, use this statement to restore it.
Sp_addextendedproc 'xp _ external shell', 'sqlsql70. dll'

Delete the xplog70.dll package. Of course, if you think you still have the possibility of using xp_cmdshell, you should change this package name ~~

Solution: run the sqlserver management tool, add a strong password to the SA account, and run the following command on the SQL command line:
"If exists (select * From DBO. sysobjects where id = object_id (N [DBO]. [xp_mongoshell]) and objectproperty (ID, nisextendedproc) = 1) exec sp_dropextendedproc N [DBO]. [xp_cmdshell]"
Go
Or: "Use master sp_dropextendedproc xp_cmdshell"
In this way, even after attackers obtain the SA account password for remote connection, they cannot call mongoshell.
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [xp_{shell] ') and
Objectproperty (ID, n' isextendedproc') = 1)
Exec sp_dropextendedproc n '[DBO]. [xp_cmdshell]'
Go

In any iSQL/osql window (or query analyzer ):
Use master
Exec sp_dropextendedproc 'xp _ export shell'
For details about sqlexecutivecmdexec, refer to the following Article :
Http://support.microsoft.com/support/kb/article/Q159/2/21.
If you do not need xp_mongoshell, disable it. Remember that a system administrator can always add it back if needed.
. This is also not good-an intruder may find that it is not there, just add it back. Consider removing the DLL in the following directory.
It must be tested before because some DLL files are used by some programs at the same time. To find out whether other programs use the same dll: first obtain the DLL.
Select O. Name, C. Text from DBO. syscomments C, DBO. sysobjects O where c. ID = O. ID and O. Name = 'xp _ your shell
Second, use the same DLL to find whether other extended storage operations use the DLL.
Select O. Name, C. Text from DBO. syscomments C, DBO. sysobjects O where c. ID = O. ID and C. Text = 'xplog70. dll'
You can use the same method to process other processes you want to remove in the following steps.
7. Disable object connection and embed Automated Storage programs if not needed (warning-some Enterprise Manager functions may be lost when these storage programs are disabled). These include:
Sp_oacreate
Sp_oadestroy
Sp_oageterrorinfo
Sp_oagetproperty
Sp_oamethod
Sp_oasetproperty
Sp_oastop
If you decide to stop the process, please write a script for them so that you can re-add them when you use them later. Remember, what we are doing here is locking an application's function-your development platform should be placed on another machine.
8. Disable the Registry access program you do not need. (As mentioned above) These include:
Xp_regaddmultistring
Xp_regdeletekey
Xp_regdeletevalue
Xp_regenumvalues
Xp_regremovemultistring
Note: I used to list xp_regread/xp_regwrite here. However, the removal of these programs affects the installation of some main functions including logs and SP, so their removal is not recommended.
9. Remove other system storage processes that you think pose a threat. Such processes are quite large, and they also waste some CPU time. Be careful not to do this on a configured server first. First, test on the developed machine and confirm that this will not affect any system functions. Below are some of the lists we recommend for your evaluation:
Sp_sdidebug
Xp_availablemedia
Xp_mongoshell
Xp_deletemail
Xp_dirtree
Xp_dropwebtask
Xp_dsninfo
Xp_enumdsn
Xp_enumerrorlogs
Xp_enumgroups
Xp_enumqueuedtasks
Xp_eventlog
Xp_findnextmsg
Xp_fixeddrives
Xp_getfiledetails
Xp_getnetname
Xp_grantlogin
Xp_logevent
Xp_loginconfig
Xp_logininfo
Xp_makewebtask
Xp_msver xp_perfend
Xp_perfmonitor
Xp_perfsample
Xp_perfstart
Xp_readerrorlog
Xp_readmail
Xp_revokelogin
XP_RUNWEBTASK
Xp_schedulersignal
Xp_sendmail
Xp_servicecontrol
Xp_snmp_getstate
Xp_snmp_raisetrap
Xp_sprintf
Xp_sqlinventory
Xp_sqlregister
Xp_sqltrace
Xp_sscanf
Xp_startmail
Xp_stopmail
Xp_subdirs
Xp_unc_to_drive
Xp_dirtree
10. Disable default logon under "Security Options" in Enterprise Manager. (Only SQL 6.5) when integrated security is used, unauthorized users who are not in the syslogins table are not authorized to log on to a valid database server.

11. Except for the Guest account of the database, unauthorized user data is excluded. The exception is the master and tempdb databases, because they are required for their guest accounts.

12. If not required, disable the SQL mail function completely. Its existence makes it possible for potential attackers to deliver potential Trojans, viruses, or simply implement a DoS attack.

13. Check master .. sp_helpstartup to see if there are any suspicious Trojan processes. Make sure no one has placed a secret backdoor program here. Use sp_unmakestartup to remove any suspicious processes.

14. Check master .. sp_password to see if Trojan existsCode. Compare your product scripts with the default scripts of a newly installed system and save it conveniently.
 
15. Record all user access information. Perform these settings from the Enterprise Manager or log in to the SA to access the following items of the query Analyzer:
Xp_instance_regwrite n 'HKEY _ LOCAL_MACHINE ', n' SOFTWARE \ Microsoft \ MSSQLServer', n' auditlevel ', REG_DWORD, 3

16. Rewrite the application to use more user-defined storage and view processes. Therefore, access to tables in general can be disabled. Here you should also see the performance improvement caused by not having to perform regular query plan operations.

17. Remove unnecessary network protocols.

18. Pay attention to the physical security of the SQL Server. Lock it in a fixed room and pay attention to key security. As long as you have the opportunity to access the server, you will always find a way to access.

19. Create a scheduled task run: findstr/C: "Login Failed" \ MSSQL7 \ log \*.*'
Then redirect the output to a text file or email, so you monitor failed login attempts. This also provides a good method for system administrators to record attacks. There are also many third-party tools used to analyze nt log events. Note: you may need to change the path to your SQL installation path.
20. set an alarm for illegal access and logon Failure logs. Go to "Manager SQL Server messages" in the Enterprise Manager to search for any messages with no access permissions (starting from "Login Failed" and "Denied ). make sure all the information you are interested in is recorded in the event log. Then, an alert is set on the information to send an email or message to an operator who can respond promptly to the problem.

21. Make sure that the roles at the server and database levels are only assigned to the desired users. When SQL Server Security Model 7 has many enhancements, It also adds an additional licensing layer. We must monitor this layer to ensure that no one has been granted more than required permissions.

22. Regular inspection teams or all members of the role are determined to assign permissions to the group, so that your audit work can be simplified. Make sure that when you are there, the public group cannot select from the system table.
23. Take some time to audit requests logging in with a blank password. Use the following code to check the empty password:
Subject
Select name,
Password
From syslogins
Where password is null
Order by name
24. If possible, use integrated security policies in your organization. By using integrated security policies, you can rely on system security to simplify management work from maintaining two separate security models. This also prevents the password from approaching the connection string.

25. Check the access process and extended storage process permissions of all non-sa users. Use the following query to regularly query which process has the public storage permission. (Use "type" instead of "xtype" in SQL Server "):
Use master
Select sysobjects. Name
From sysobjects, sysprotects
Where sysprotects. uid = 0
And xtype in ('x', 'P ')
And sysobjects. ID = sysprotects. ID
Order by name

26. When enterprise manager was used, integrated security policies were used. In the past, Enterprise Manager was found to store the "sa" password in the plaintext of the Registry in the standard security mode. Note: Even if you change the mode, the password will remain in the registry. Use regedit and check the key:
HKEY_CURRENT_USER \ Software \ Microsoft \ MSSQLServer \ sqlew \ regedi \ SQL 6.5
Now the data is hidden in HKEY_USERS \ {yoursid} \ Software \ Microsoft SQL Server \ 80 \ tool \ sqlew \ registered server x \ SQL Server group ("SQL Server group" is the default value however, you may have created a user group to change its location accordingly)

27. Develop an audit plan and set up monthly security reports. reports available to it supervisors include any new exploit, successful attacks, backup protection, and Object Access failure statistics.

28. Do not allow users to log on to SQL Server interactively. This rule applies to any server. Once a user can interactively enter a server, the administrator privilege can be obtained.

30. Try your best to restrict the query and access operations on SQL Server. You can use the minimum permission to query many things in SQL Server. If not, do not give them a chance.