| SQL Server is a complex and versatile program, and it would be foolish to think we could explain all of its intricacies in one chapter. However, the authors have noticed some common issues people run into when getting started. In this section we will attempt to give you some hints if you are having trouble connecting to or using your SQL server. Understanding Login Options As you may recallfrom Figure 20.12, when you connect to a database, you have the option of using integrated security or entering a user name and password defined in the database. Integrated security means that your Windows login account has the authority defined on the database server. For example, my Windows user id is bsiler so I could allow access to this account on the SQL server. Another method of establishing access, the one recommended by the author, is to define a database login within SQL Server itself. These logins are independent of Windows user accounts. For example, I could create an employee user login within SQL Enterprise Manager, and anyone could use this login to access the employee database, independent of how he or she is logged in to his or her PC. The advantage of using the database login method is most of the applications you will be developing probably will not require the user to connect to the database directly, so there is no reason to maintain individual user accounts. For example, when your Web server connects to the SQL server to retrieve information, the Web server is making the connection to the database, not the user browsing the Web site. (Of course, this does not mean you have to drop Windows authentication entirely, the Web page could be secured using it.) By default, SQL Server includes the sa login, which stands for system administrator. This login has full access to all databases and SQL Server operations. For the purposes of development on your own PC on an internal network, you can use this login to avoid security restrictions. However, when you move to a production environment, be sure to create a new login with security restricted to only the necessary database objects. Note A lot of people have left their sa passwords blank! This is a sure recipe for disaster don't do it!
Network Connection Problems In addition to a login and password, there are several different protocols available that you can use to connect to a SQL server. A protocol is just a set of rules for two computers to exchange information. Whoever installs the SQL Server software has the option of enabling or disabling these protocols. The most common protocols are the following: Named Pipes This protocol works very similarly to mapping a drive in Windows NT, in that it uses your Windows NT login and password to establish a connection with the server. TCP/IP This protocol works like browsing a Web site. You connect via an IP address and port number.
One of the most frequent causes of connection problems is attempting to connect with Named Pipes without sufficient authority. Even if you have the sa login to SQL Server, if you do not have NT access to the communication pipe, you may receive messages such as "access denied" or "Connection Open:createfile( )." The fastest and most trouble-free protocol is TCP/IP. To make sure you are connecting via TCP/IP, open the SQL Client Network Utility, which should be located in your Microsoft SQL Server program group. Within this utility, you can set the protocol, IP, and Port information for individual SQL servers. Specifying SQL Server Instances When referring to a particular database server with which they want to interact, developers often refer to the server by computer name alone. However, SQL Server 2000 allows you to run multiple copies (or instances) of the SQL Server service on the same physical computer. Each instance contains its own set of databases and is generally independent from other instances on the computer. (From a TCP/IP point of view, each instance runs on a separate port.) If you configured the .NET SDK samples included with Visual Studio, the installation process created an instance of SQL Server called NetSDK on your machine. To specify an instance name when connecting to a SQL server you use a backslash, as in the following example: MyServer\NetSDK The machine name in the example is MyServer and the instance name is NetSDK. When the database administrator sets up a new instance, he specifies the instance name and port, which must be unique on the machine. Each computer running SQL Server can also have one default instance, which works just like previous versions of SQL Server; you do not need to specify an instance name when connecting to the default instance. | 
