MS SQL Server

If you want to change the name of an existing database user, you can use the "ALTER USER" statement as shown in the tutorial exercise below:

-- Login with "sa"

USE GlobalGuideLineDatabase;

GO

ALTER USER ggl_User WITH NAME = Dba_User;

GO

-- List all user names

SELECT name, sid, type, type_desc

FROM sys.database_principals WHERE type = 'S';

GO

name    sid    type type_desc

-------------------- ------------------------ ---- ---------

dbo   0x01    S SQL_USER

guest   0x00    S SQL_USER

INFORMATION_SCHEMA NULL    S SQL_USER

sys   NULL    S SQL_USER

Dba_User   0x5EB8701EAEBAA74F86F... S SQL_USER

The of the last user changed from "ggl_User" to "Dba_User".

If you know a user name in a database and you want to find out which login name is linked this user name, you need to check the Security ID (SID) of the user name based on the following rules:

* Each login name is associated a unique SID.

* When a user name is linked to a login name, the login name's SID is copied to the user name.

So the login name linked to a user name must have the SID as the user name. The tutorial exercise below shows you how to find the login name that is linked to the user name "ggl_User":

-- Login with sa

USE GlobalGuideLineDatabase;

GO

SELECT u.name AS User_Name, l.name AS Login_Name, u.sid

FROM sys.server_principals l,

sys.database_principals u

WHERE l.sid = u.sid

AND u.name = 'ggl_User';

GO

User_Name Login_Name sid

---------- ----------- ----------------------------------

ggl_User ggl_Login 0x5EB8701EAEBAA74F86FCF5BD8E37B8C5

(1 row(s) affected)

If you don't want to keep a database user any more, you should delete the user by using the "DROP USER" statement. This tutorial exercise shows how to delete "Dba_User":

-- Login with "sa"

USE GlobalGuideLineDatabase;

GO

DROP USER Dba_User;

GO

-- List all user names

SELECT name, sid, type, type_desc

FROM sys.database_principals WHERE type = 'S';

name    sid    type type_desc

-------------------- ------------------------ ---- ---------

dbo   0x01    S SQL_USER

guest   0x00    S SQL_USER

INFORMATION_SCHEMA NULL    S SQL_USER

sys   NULL    S SQL_USER

User "Dba_User" has been deleted now.

Assuming that the ODBC driver for SQL Server has been installed as part of the Windows system, the next step of setting up ODBC connection to SQL Server is to create a new DSN (Data Source Name) with the ODBC Data Source Administrator:

* Go to Control Panel.

* Go to Administrative Tools.

* Run Data Sources (ODBC). The ODBC Data Source Administrator window shows up.

* Go to System DSN tab.

* Click the Add button.

You should a list of all ODBC drivers installed on your system. SQL Server ODBC driver should be on the list. If you look at the picture below, you will see the SQL Server ODBC driver with version 2000.85.1117.00, file SQLSRV32.DLL, date 8/4/2004:

ODBC Driver for SQL Server

Select "SQL Server" from the ODBC driver list, and click "Finish" button. The "Create a New Data Source to SQL Server" wizard window shows up. Continue with the next tutorial to finish up creating a new DSN.

Continue from the previous tutorial. After clicking Next on the first screen of the "Create a New Data Source to SQL Server" wizard, you should see the second screen asking you to select SQL Server login type, login name and password.

Select the radio button on "With SQL Server authentication using a login ID and password entered by the user. Also enter in Login ID field: "sa" and in Password field: "GlobalGuideLine". See the picture below:

ODBC DSN Login Information

Remeber that "sa" is the system administrator login name, you probably should use a less privileged login name here. "GlobalGuideLine" must be the correct password defined in the SQL Server for "sa" login name.

Click Next and continue with the next tutorial to finish up creating a new DSN.

If you want to see a list of all user names defined in a database, you can use the system view, sys.database_principals as shown in this tutorial exercise:

-- Login with sa

-- Select a database

USE GlobalGuideLineDatabase;

GO

-- List all user names

SELECT name, sid, type, type_desc

FROM sys.database_principals WHERE type = 'S';

GO

name    sid    type type_desc

-------------------- ------------------------ ---- ---------

dbo   0x01    S SQL_USER

guest   0x00    S SQL_USER

INFORMATION_SCHEMA NULL    S SQL_USER

sys   NULL    S SQL_USER

ggl_User   0x5EB8701EAEBAA74F86F... S SQL_USER

(5 row(s) affected)

As you can see, there are 5 user names defined in "GlobalGuideLineDatabase". 4 are defined by SQL Server during the database creation process. Only the last one "ggl_User" was defined by you in the previous tutorial.

ODBC, Open Database Communication, a standard API (application program interface) developed by Microsoft for Windows applications to communicate with database management servers.

If you want to access a database server through an ODBC driver from an application program, you need to meet the following requirements:

* An ODBC driver specifically designed for the database server.

* An ODBC DSN (Data Source Name) - an ODBC configuration representing the ODBC driver and database server.

* An ODBC API (Application Programming Interface) for your application program to interact with database server through the ODBC driver.

For example, if you want to a SQL Server in a PHP script through an ODBC driver, you need to make sure that:

* An ODBC driver for MS SQL Server - The ODBC driver is installed as part of the Windows system.

* An ODBC DSN - You need to create one yourself using the ODBC configuration tool.

* An ODBC API in PHP language - The API is installed as part of the PHP engine.

By default, SQL Server 2005 Express Edition is installed only one connection protocol enabled:

* Shared Memory - SQL Server connection protocol for applications that are running on the same machine where the SQL Server is running. For example, if you are running SQLCMD tool on the SQL Server machine, it will use the "Shared Memory" protocol.

If you want application that are running remotely to connect and access the SQL Server you need enable the "TCP/IP" protocol.

For applications that are running on the same machine as the SQL Server, but they do not support "Shared Memory" protocol, you also need to enable the "TCP/IP" protocol for those applications. ODBC Manager is an application that requires the "TCP/IP" protocol to connect to the SQL Server.

In order for the SQL Server to accept network connections, you also need to run SQL Server Browser Service on the SQL Server machine.

In summary, there are two requirements for a SQL Server to accept network connections:

* Start SQL Server Browser Service on the SQL Server machine.

* Enable the TCP/IP protocol support on the SQL Server.

Continue from the previous tutorial. After clicking Next on the SQL login information screen, ODBC manager will try to connect to the SQL Server with the login information you provided.

After a period of waiting time, you may get error message box saying that:

Connection failed:

SQLState: '01000'

SQL Server Error: 53

[Microsoft][ODBC SQL Server Driver][DBNETLIB]

ConnectionOpen(Connect()).

Connection failed:

SQLState: '08001'

SQL Server Error: 17

[Microsoft][ODBC SQL Server Driver][DBNETLIB]

SQL Server does not exist or access denied.

See the picture below:

ODBC DSN Connection Failed

Three possible reasons for the failing:

* Wrong server name - You provided an incorrect server name.

* SQL Server not configured to take a network connection - You need to check the SQL Server configuration.

* Wrong login name or password - You provided incorrect login name or password.

The first and third reasons are easy to validate and correct. The second reason requires further investigation. Continue with the next tutorial to configure your SQL Server to take a network connection.

By default, the TCP/IP protocol is turned off when a SQL Server is installed to reduce security risk. But if you want applications to connect and access the SQL Server, you need to enable the TCP/IP protocol on the server by following this tutorial:

1. Go to Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager.

2. Double click on "SQL Server 2005 Network Configuration" to see the list of SQL Server instances.

3. Click on "Protocols for SQLEXPRESS". You will see a list of protocols.

4. Right-mouse click on TCP/IP and select the "Enable" command. See the picture below:

TCP/IP Protocol Enabled

5. Click on "SQL Server 2005 Services". You will see two services.

6. Right-mouse click on "SQL Server (SQLEXPRESS)" and select restart to finish the TCP/IP protocol setting change.

Your SQL Server is ready to accept network connection now.

If your SQL Server is not using the default port number, like 1269, you need to set the port number to the correct value during the ODBC DSN creation process, as show in this tutorial:

1. Start ODBC Data Source Administrator and click System DSN tab.

2. Click Add button, select SQL Server and click Finish button.

3. Enter the following and click Next:

Name: ggl_SQL_SERVER

Description: GlobalGuideLine.com SQL Server

Server: LOCALHOST

4. Select the radio button on "With SQL Server authentication using a login ID and password entered by the user. Also enter in Login ID field: "sa" and in Password field: "GlobalGuideLine".

5. Click "Client Configuration..." button. The "Edit Network Library Configuration" dialog box shows up.

Check and uncheck "Dynamically determine port" and enter "1269" as the Port Number. See picture bellow:

ODBC DSN Port Number Setting

Click OK and continue with the next tutorial to finish up ODBC DSN creation.

When applications use TCP/IP for network communication, you need to know the port number where the server is listening for connect request from the client.

If you want to connect to the SQL Server through the TCP/IP, you must know on which port number the SQL Server is listening for connection requests. Follow this tutorial to find and verify the port number of your SQL Server.

1. Go to Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager.

2. Double click on "SQL Server 2005 Network Configuration" to see the list of SQL Server instances.

3. Click on "Protocols for SQLEXPRESS". You will see a list of protocols.

4. Right-mouse click on TCP/IP and select the "Properties" command. The properties dialog box shows up.

Click the IP Address tab. The server IP address and port number used by the SQL Server will be displayed. You should see something like:

IP Address: 127.0.0.1

Ports: 1269

Note that 1433 may also be used as the default port number by your SQL Server.

Continue from the previous tutorial. Click Next after you have finished changing the port number. The ODBC Data Source Administrator will try to connect to the SQL server through the specified port number and login information.

If the connection is successful, you will see the next screen asking for selecting default database.

1. Check "Change the default database to:" check box, and select "GlobalGuideLineDatabase" as the default database for this DSN setting. See picture bellow:

ODBC DSN Default Database Selection

2. Click Next to see the last screen of the "Create a New Data Source to SQL Server" wizard.

3. Change nothing on the last screen and click Finish button. The confirmation screen shows up.

4. Click "Test Data Source..." button. You should see the test result as:

Attempting connection

Connection established

Verifying option settings

Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

Your ggl_SQL_SERVER ODBC DSN is ready to use.

Once you got a DSN defined in the ODBC manager that connects to your SQL Server, you can connect a normal MS Access document to the Oracle server, and link an Access table to a SQL Server table. The tutorial below gives you a good example:

* Start MS Access with a new database file.

* Go to File menu.

* Select Get External Data.

* Select Import.... The Import dialog box shows up.

* Select Files of type: ODBC Database(). The Select Data Source dialog box shows up.

* Click the Machine Data Source tab. You should see the DSN name "ggl_SQL_SERVER" you defined earlier.

* Select "ggl_SQL_SERVER".

* Enter User Name: sa.

* Enter Password: GlobalGuideLine.

Click the OK button to continue. You should see a list of tables available for you to import from the SQL Server as shown in the picture below:

MS Access importing tables via ODBC

Select the table you are interested in and click OK. You should the selected table being imported from the SQL Server to MS Access.

If you are planning to use ODBC connections to access SQL Server databases in PHP scripts, you need to check the following requirements:

* The PHP engine must support ODBC functions. If you install PHP 5.2.2 from The PHP Group, the ODBC functions are already included in the default installation.

* The SQL Server must have TCP/IP protocol enabled for a specific port.

* The SQL Server Browser Service must be running on the SQL server machine.

* An ODBC DSN must be created on machine where you are running PHP scripts.

* The ODBC DSN connection must be tested to make sure it is working.

You need to following other tutorials provided by GlobalGuideLine.com to install PHP engine, install SQL Server, configure TCP/IP protocol, start SQL Server Browser Service, create and test ODBC DSN connections.

If you look at the PHP 5 manual, you will see a group of functions listed under the ODBC Functions (Unified) setion. The commonly used ODBC functions are:

* odbc_connect ? Establish an OBDC connection.

* odbc_data_source ? Returns information about a current connection.

* odbc_close ? Close an ODBC connection.

* odbc_exec ? Prepare and execute a SQL statement.

* odbc_fetch_row ? Fetch a row - moving the pointer to the a new row in a result object.

* odbc_result ? Get the value of a specific field from the current row of a result object.

* odbc_fetch_array ? Fetch a result row as an associative array.

* odbc_fetch_object ? Fetch a result row as an object.

* odbc_num_rows ? Number of rows in a result.

* odbc_field_name ? Get the name of a specified field index.

* odbc_field_type ? Get the data type of a specified field index.

* odbc_next_result ? Checks if multiple results are available.

* odbc_free_result ? Free resources associated with a result object.

* odbc_prepare ? Prepares a statement for execution.

Assuming you have followed other GlobalGuideline.com tutorials and created an ODBC DSN called "ggl_SQL_SERVER", and planning to use it your PHP scripts, you should test this ODBC connection first as shown in this tutorial:

1. Go to Control Panel > Administrative Tools.

2. Run Data Sources (ODBC). The ODBC Data Source Administrator window shows up.

3. Click "System DSN" tab, select "ggl_SQL_SERVER", and click "Configure..." button. The Microsoft SQL Server DSN Configuration wizard window shows up.

4. Review the first screen and click Next.

5. Review the second screen, enter the password as "GlobalGuideLine", and click Next.

6. Review the third screen and click Next.

7. Review the fourth screen and click Finish.

8. Review the confirmation screen and click "Test Data Source...". You should see the test result as:

Attempting connection

Connection established

Verifying option settings

Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

Your ggl_SQL_SERVER ODBC DSN is tested and ready to use.