MS SQL Server 2012: network access configuration not working
I've installed Microsoft SQL Server 2012 Express with Advanced Services on Windows 7 64-bit. During installation I chose to configure the SQL Server instance as the "Default instance", and as a result I am able to connect to it using TCP/IT by specifying either "localhost", "(local)", "MyComputerName", or "." in the "Server name:" field in Microsoft SQL Server Management Studio (in each case the connection shows up on the left-hand column of SQL Server Management Studio as "SQL Server 11.0.3128 - MyComputerName\MyUserName". However when I access the Services window from the Control Panel's Administration Tools, I can see that the installed SQL Server instance is running using the connection name "NT Service\MSSQLSERVER". This corresponds to the "Instance ID: MSSQLSERVER" which appeared automatically during installation when I chose "Default instance". What I would like to ask is:
Given this configuration, why can't I connect by specifying MSSQLSERVER or alternatively NT Service\MSSQLSERVER in the server name field in SQL Server Management Studio? How can I get this alternative connection mechanism to work (without recreating a new SQL Server instance)?
Perhaps I don't fully understand how connections, connection strings, and connection types work under Winodows, and what the underlying Windows-specific connection protocols are. Perhaps someone can point these details out and sort out any confusing issues related to these which arise when someone know about TCP/IP but not so much about proprietary Windows network protocols.
Thanks.
Answer
I think you are confused between the two connection authentication types:
Windows authentication
SQL server authentication
to access SQL server remotely you will have to create a SQL user account or to access it from a windows authenticated user.
Windows authentication: Let's say you have a domain controller that has a group that called MSSQL_USERS, then you'll have to add this group in the SQL server and give the right access to it, after that you can connect to sql server remotely using windows authentication sure after logging to the Windows by a user that belongs to that group.
SQL server authentication: MS SQL server gives you the possibility to create users with passwords and give them access to the needed access, databases, tasks...Create an account on an SQL server instance with a password will give you the access to connect to that instance.
now if your instance called "MSSQLSERVER" so you must put it after the server name\IP address
e.g: 192.168.3.9\MSSQLSERVER
or SQL_SERVER\MSSQLSERVER
if you are working locally it is not necessary to put the server name. if the server name didn't work then you may have an issue with your DNS, putting the IP address is always safer and will solve this kind of problems.
To enable the network connection to the server you gonna have to enable the remote connections to the server from :
1- right click on the server > go to properties
2- go to connections and enable the remote connections
During setup, you must select an authentication mode for the Database Engine. There are two possible modes: Windows Authentication mode and mixed mode, the MIXED mode that enables you to connect to SQL server by both types SQL authentication and windows authentication, if that option wasn't in the setup program you have, then you should go to the
server properties>security tab>server authentication
also when you install MS SQL server you can assign the password for the "sa" user which is the SQL server administrator, but that will be enabled only when mixed mode is enabled because the windows authentication mode will disable the SQL server authentication.