I had a scare with a trojan horse rootkit virus right about the time I
started playing with SQL Server Express 2005 and programming it
(accessing it) using C++.NET (see below my demonstration program,
which works).  It appears the virus was a false positive, but it got
me thinking about security when programming a database.

My question is:  I'm inside a firewall, and the database is acting as
a server using "Windows Authentication" and SSPI (rather than "SQL
Server Authentication" which requires a User Name and password).  I'm
accessing the database from a C++.NET shell program (see below).

How secure is this? Can somebody access the database from outside, if
they don't know my password used to logon to my account (Power User)?
I assume that unless I give permission for the program to search
outside the firewall, that the firewall will stop anybody from
accessing the database (first assumption), then, even if the program
and/or database can search or be searched from outside the firewall,
that "Windows Authentication" will stop anybody from accessing the
database unless they have the proper permission, such as the ability
to access my PC remotely using my logon password (which is the
responsibility of the OS I suppose, not my job).

Below is some info on SSPI I got from Wikipedia.  SSPI is used in the
Connection String of the below demonstration program.

RL

/*
http://en.wikipedia.org/wiki/Windows_Integrated_Authentication

SSPI is a programming API used by Microsoft Windows systems to perform
a variety of security related operations such as authentication. The
tokens generated and accepted by the SSPI are mostly compatible with
the GSSAPI (e.g. an SSPI client on Windows can authenticate with a
GSSAPI server on UNIX).

http://en.wikipedia.org/wiki/Generic_Security_Services_Application_Program_Interface

The Generic Security Services Application Program Interface (GSSAPI,
also GSS-API) is an application programming interface for programs to
access security services.

The GSSAPI, by itself, does not provide any security. Instead,
security service vendors provide GSSAPI implementations usually in the
form of libraries installed with their security software. These
libraries present a GSSAPI-compatible interface to application writers
who can write their application to use only the vendor-independent
GSSAPI. If the security implementation ever needs replacing, the
application need not be rewritten.

The definitive feature of GSSAPI applications is the exchange of
opaque messages (tokens) that hide the implementation detail from the
higher level application. The client and server sides of the
application are written to convey the tokens given to them by their
respective GSSAPI implementations. GSSAPI tokens can be sent over an
insecure network because the mechanisms guarantee inherent message
security. After some number of tokens have been exchanged, the GSSAPI
at both ends inform their local application that a security context
has been established.

Once a security context is established, sensitive application messages
can be wrapped (encrypted) by the GSSAPI for secure communication
between client and server. Typical protections guaranteed by GSSAPI
wrapping include confidentiality (secrecy) and integrity
(authenticity). The GSSAPI can also provide local guarantees about the
identity of the remote user or remote host.

*/


\\:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data //this is
my path

/*
Data Source=MyPentiumPCNameHere\SQLEXPRESS;Initial
Catalog=DCV_DB;Integrated Security=True;Pooling=False << This is my
Connection String (says SQL Server 2005 Express, under “Properties”)
//
// This is my source code (compiles and works OK)

*/

////
using namespace System;
using namespace System::Data;
using namespace System::Data::SqlClient;
//above namespaces recommended as default by textbook (Frasier)
////////////

void main()
{
    SqlConnection^ connection = gcnew SqlConnection();


connection->ConnectionString = "Persist Security Info = False;
Integrated Security=SSPI;"   "Data Source=(local)\\SQLEXPRESS;
Initial Catalog=DCV_DB;";

// double backslash makes ConnectionString work !

        try
    {
        connection->Open();
        Console::WriteLine("We got a connection!"); //THIS BRANCH
WORKED!
    }
    catch (SqlException ^e)
    {
        Console::WriteLine("No connection the following error
occurred!!!: {0}",
            e->Message);
    }
    finally
    {
        connection->Close();
        Console::WriteLine("The connection to the database has been
closed");
    }

}

/////////////

Hi

Show quoteHide quoteWindows provides a better machanism for storing passwords and validating a
user, the ability to set password complexity and ageing also gives better
protection. This is less with SQL 2005 if you set up a login to enforce the
password policy rules. Even if you only have Windows Authentication, it does
not mean that you should ignore good security practices when assigning access
and permissions to the SQL Server Instance or database, and just because
someone can log in to a given domain/machine should not mean they do have
access to the databases on that system.

From a risk perspective, having two methods of authentication is a higher
risk than having one, therefore you should need to justify if you need SQL
Authentication.

HTH

John

"Orphaned" maintenance plan
How to Diagnose IO Performance On A SAN
Using Ansi_Padding Off
Log Shipping from SQL Server 2000 Enterprise to SQL Server 2005 Standard
Dump log and move log to another drive
DB Backup jobs
Best way to maintain up to date SQL Jobs and SSIS on failover Serv
Linked server SQLNCLI error
Renaming A Server
sp_spaceused - need help interpreting