MSSQL Server Service Information

Security model and accessing your database

Internet-Sites.com MS SQL Server service employs a mixed security model which utilizes Trusted and Non-trusted connections. The non trusted connection is aliased as DBO, so by using this as your login, full administration of your database is possible. Here are are your login parameters:

User: <userid>sql
PWD: <your password to the left of the +, if it exists>
Database: <userid>

For example if you had a user id of ntrules and a password of nteasy+unixnot, your login parameters would be as follows:

User: ntrulessql
PWD: nteasy
Database: ntrules

You can create your complete schema in this database using this login. Access through the web pages should be through Trusted connections utilizing the guest account. You should set appropriate permissions to the guest account for all the objects that you create that you wish web browsers to access. There is no need to set a UID or PWD in the connection strings for guest access.

If you require a custom security configuration let us know and we will supply additional Non-Trusted logins for your use.

Web Interface
A web interface has been supplied for your use at stats/sqlquery.asp. With this interface you can send any scripting commands to your database. The Active Server Page "sqlquery.asp" can also be used as an example of database access through ASP.

The control panel gives you a way of defining custom ODBC Datasources for your applications. There is also a standard "ntsql" DSN available for your use.

Sample Database upload using BCP

Internet-Sites.com supplies an ASP form that gives you access to the BCP function, it is called using stats/sqlbcp.asp. The following is an example of a method of transferring your data, we will look at the following steps:

1. Using the MSSQL Enterprise manager to generate scripts for database structure and permissions.
2. Executing these scripts through the ASP sqlquery.asp.
3. Using BCP to generate a .FMT and .TXT file.
4. Using the stats/sqlbcp.asp form to upload the data to your Internet-Sites.com MSSQL Server table.

1. Using the MSSQL Enterprise manager to generate scripts for database structure and permissions.
Select the database you want to work with, for the purpose of this example we will use the publishers table found in the sample pubs database that ships with SQL Server. This option is available to you in the MSSQL Enterprise Manager by selecting the "Object" menu and selecting "Generate Scripts". The options that were selected for this sample are Object Creation, Object Drop, Table Indexes, Table Keys/DRI and Permissions. The only object selected is the publishers table. The script generator generates the following script:

/****** Object: Table dbo.publishers Script Date: 7/5/97 4:44:27 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.publishers') and sysstat & 0xf = 3)
drop table dbo.publishers
GO

/****** Object: Table dbo.publishers Script Date: 7/5/97 4:44:27 PM ******/
CREATE TABLE dbo.publishers (
pub_id char (4) NOT NULL ,
pub_name varchar (40) NULL ,
city varchar (20) NULL ,
state char (2) NULL ,
country varchar (30) NULL CONSTRAINT DF__publisher__count__09897611 DEFAULT ('USA'),
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
(
pub_id
),
CONSTRAINT CK__publisher__pub_i__089551D8 CHECK (pub_id = '1756' or (pub_id = '1622' or (pub_id = '0877'
or (pub_id = '0736' or (pub_id = '1389')))) or (pub_id like '99[0-9][0-9]'))
)
GO

GRANT REFERENCES , SELECT ON publishers TO guest
GO

2. Executing these scripts through the ASP sqlquery.asp.

To work in the asp interface, you need to get rid of the GO statements and comments. Here is what is pasted into the SQLQUERY.ASP SQL Statement field:

if exists (select * from sysobjects where id = object_id('dbo.publishers') and sysstat & 0xf = 3)
drop table dbo.publishers

CREATE TABLE dbo.publishers (
pub_id char (4) NOT NULL ,
pub_name varchar (40) NULL ,
city varchar (20) NULL ,
state char (2) NULL ,
country varchar (30) NULL CONSTRAINT DF__publisher__count__09897611 DEFAULT ('USA'),
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
(
pub_id
),
CONSTRAINT CK__publisher__pub_i__089551D8 CHECK (pub_id = '1756' or (pub_id = '1622' or (pub_id = '0877' or (pub_id = '0736' or (pub_id = '1389')))) or (pub_id like '99[0-9][0-9]')))

GRANT REFERENCES , SELECT ON publishers TO guest

Executing this creates the table, indexes, constraints and permissions on your Internet-Sites.com MSSQL database

3. Using BCP to generate a .FMT and .TXT file.

Execute the following line on your local SQL Server:

bcp pubs..publishers out publishers.txt -Usa

When you execute this you will be prompted for a password, after entering the password you will be prompted for an output .FMT file format to be generated. The only entries required in generating the FMT file are entering \T for the field terminators except for the last field "country" which is a \N, take all the remianing defaults by simply presing enter. Name the file publishers.fmt. After BCP finishes you will have two files publishers.txt and publishers.fmt.

4. Using the stats/sqlbcp.asp form to upload the data to your Internet-Sites.com MSSQL Server table.

Copy the publishers.txt and publishers.fmt files to your web root. Access the stats/sqlbp.asp form and make the following entries:

Password: <your password>
Table you are updating on MSSQL: publishers
Text file where you data is: publishers.txt
FMT file name: publishers.fmt

After pressing submit, you will see the results of your BCP operation. When you are done, test the guest access by going into the stats/sqlquery.asp form and perform a "select * from publishers" using guest access.

SQL Access:

We have implemented the capability to access your SQL Server databases with the following products:

• Visual InterDev (the preferrred method)
• SQL Server Enterprise Manager (pretty slow over a 28800 connection)
• ISQL/w (very fast)

This access has only been confirmed with a MSN Dial Up Internet PPP connection, your ISP has to have the proper ports open for this to happen. Maybe as more people connect and test it, they will post which ISPs can handle this connection

Here is what you need to do to create a connection over the Internet, please follow these instrutions fully before even attempting the connection.

Set your SQL Server client configuration to use TCP/Sockets as it's default network by following these steps:

1. Navigate into the SQL Server 6.5 program group and select SQL Server Client Configuration.
2. Select the Net Library tab and change the Default Network to TCP/IP Sockets.

Be aware that if you are connecting to your SQL Server locally and not using TCP/IP Sockets as a network protocol, your local connection will not work anymore. Note that the default net library used with SQL Server 6.5 is Named Pipes and not TCP/IP Sockets.

Also, be aware that this is an open Internet Connection and you are passing your user id and password over it.

You will now be able to connect to SQL Server with the aforementioned products using the SQL user id and password that was supplied to you.