MSSQL 2008 cannot use IP logon problem how to solve _mssql2008

Source: Internet
Author: User
Tags sql 2008 mssql management studio sql server management sql server management studio port number
See a lot of people on the internet asked why my SQL 2008 can only use the server calculator name login, but not IP login? I have also encountered this problem, see online answers vary, but none of them are easier to write. After a network of keywords (SQL 2008 local login) to find articles, collation, try, finally my SQL 2008 can be used IP address or local login, thanks to those unsung heroes.

Finally, my success process I dare not a person to stash, deliberately sorted out the solution process with everyone to share.

Solve the problem that MSSQL 2008 cannot log on with IP

Prerequisite Preparation: MSSQL 2008 has an instance installed (I installed the instance by default: SQLEXPRESS) and installed the SQL Server Management Studio tool.
The following configuration support is required in order to be able to log on to 2008 with a (local) or IP address:

First, open SQL Server Configuration Manager, and make the following configuration
1, click on the "SQL Server Services" node
Locate the SQL service that we installed: SQL SERVER (SQLEXPRESS), double-click it to open the Properties Settings dialog box, select the built-in account in the login ID, and drop down to select "Network Service": It means for network services, This allows remote clients to restart the SQL Server service, otherwise the restart service function is limited to this computer.
2. Click the SQL Server Network Configuration node.
Click on the "Instance Name Protocol" (mine is the SQLExpress agreement). Can see my example is supported share Memory, Named pipes, TCP/IP, via these several protocols.
Right-click "TCP/IP" to select Enable. Right-click it to select Properties, in the Properties dialog box, switch to the "IP address" card, you can see "IP1", "IP2" ... "Ipall" These categories of IP-specific settings information. First of all, because we currently do not use the "TCP Dynamic port" feature, so the TCP dynamic port for these types of IP is set to null (if it is 0, the dynamic port feature is enabled). And because I'm going to set the SQLExpress instance the port that provides the service out to 1433 (consistent with the default service connection port number of the SQL2000), So it is necessary to set their TCP port to 1433 for all kinds of IP (in fact, only need to set the TCP number of Ipall to 1433, all other types are empty). Then the various types of IP "active" and "enabled" are set to Yes. Click "OK" to save the configuration.
3. Click on the "SQL Native client 10.0 Configuration" node (if not, your software may be installed with problems, or there is no client tool installed: SQL Server Management Studio).
Click on "Client protocol" to find "TCP/IP", double-click it to open the Properties dialog box, set "Default port" to 1433 (as long as the configured port number is consistent), set "Enabled" to "yes". Click OK to save the configuration.
4, restart SQLExpress instance.

Second, open the SQL Server Management Studio Tool
In the Connect to Server Actions dialog box, configure the following:
Server type: Database engine
Server name: (local) or IP address or. To mean the meaning of the machine
Click on the lower right corner of the "Options >>" button, switch to the "Connection properties" card, in the network protocol a configuration Drop-down select "TCP/IP"
Click the Connect button.
To open a method that you can log on with an SA

If you do not open a user (not necessarily an SA), there is no way to connect to the SQL Server server for remote client tools that are not on the server.
a, first log on to the server SQLExpress instance with the SQL Server Management Studio tool (either by IP logon or by computer name).
two, right-click on the root node to open the Properties dialog box, locate the Security node, open it, set server Authentication to SQL Server and Windows Authentication mode, and then click OK to save the configuration.
three, expand: Root node-> security-> logon name->sa. Right-click the SA to open the Properties dialog box.
1, in the General page set the SA user's password, note that the password can not be too simple, such as the password is SA must not pass.
2, in the "User Mapping" page, confirm that the SA user has no db_owner role identity. It has to be, otherwise the advanced functionality will not be available after the SA is logged in.
3. On the Status page, set the "Allow connection to Database engine" setting to "grant" and set "login" to "enabled".
4, click OK to save the configuration.
FourAnd then you can try to log in with the SA user. This is similar to the SQL Server2000.
Special Note:

The above views are personal experience, it is inevitable that there is no understanding of the place, if there is a mistake, hope that heroes do not use the word "dirty" curse:), please reply or comment.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.