SQL Server security model

SQL Server security model

In order to access data from a database, a user must pass through two stages of authentication. first one at the SQL Server level and the other at the database level. These two stages are implemented using Logins names and User accounts respectively.

A valid login is required to connect to SQL Server and a valid user account is required to access a database.
How to Login: A valid login name is required to connect to an SQL Server. A login could be:

• A Windows NT/2000 login that has been granted access to SQL Server
• An SQL Server login, that is maintained within SQL Server
These login names are maintained within the master database. So, it is needed to backup the master database after adding new logins to SQL Server to the machine.

User: A valid user account within a database is required to access that database. User accounts are specific to a database. All permissions and ownership of objects in the database are controlled by the user account. SQL Server logins are associated with these user accounts. A login can have associated users in different databases, but only one user per database.
During a new connection request, SQL Server verifies the login name supplied, to make sure, that login is authorized to access SQL Server. This verification is called Authentication. SQL Server supports two authentication modes:

• Windows authentication mode: With Windows authentication, you do not have to specify a login name and password, to connect to SQL Server. Instead, your access to SQL Server is controlled by your Windows NT/2000 account (or the group to which your account belongs to), that you used to login to the Windows operating system on the client computer/workstation. A DBA must first specify to SQL Server, all the Microsoft Windows NT/2000 accounts or groups that can connect to SQL Server

• Mixed mode: Mixed mode allows users to connect using Windows authentication or SQL Server authentication. Your DBA must first create valid SQL Server login accounts and passwords. These are not related to your Microsoft Windows NT/2000 accounts. With this authentication mode, you must supply the SQL Server login and password when you connect to SQL Server. If you do not specify SQL Server login name and password, or request Windows Authentication, you will be authenticated using Windows Authentication.
Point to note is that, whatever mode you configure your SQL Server to use, you can always login using Windows authentication.

Windows authentication is the recommended security mode, as it is more secure and you don't have to send login names and passwords over the network. You should avoid mixed mode, unless you have a non-Windows NT/2000 environment or when your SQL Server is installed on Windows 95/98 or for backward compatibility with your existing applications.

Read More

Sql Server Hosting , web hosting

Key SQL Server Hosting Features (2008)

SQL Server 2008 64-bit

Language Integrated Query (LINQ) in sql server hosting
Developers can use queries against data, with using a managed programming language, instead of SQL statements. Developers are enabled to use strongly typed, set-oriented queries to run against ADO.NET (LINQ to SQL), ADO.NET DataSets, the ADO.NET Entity Framework, and to the Entity Data Service Mapping provider. A new LINQ to SQL provider enables developers to use LINQ directly on SQL Server 2008 tables and columns.

New Data Types in sql server hosting
SQL Server 2008 introduces new date and time data types: DATE, TIME, DATETIMEOFFSET, DATETIME2. The new data types enable applications to have separate data and time types.

Spatial Data Types help build spatial capabilities into applications by using the support for spatial data. Customers can implement Round Earth solutions with the geography data type, using latitude and longitude coordinates to define areas on the Earth's surface. Customers can also implement Flat Earth solutions with the geometry data type, storing polygons, points, and lines that are associated with projected planar surfaces and naturally planar data, such as interior spaces.

Integrated Full Text Search in sql server hosting
Integrated Full Text Search makes the transition between Text Search and relational data seamless.


Sparse Columns in sql server hosting
NULL data consumes no physical space, providing an efficient way of managing empty data without experiencing large space costs.

Large User-Defined Types in sql server hosting
SQL Server 2008 eliminates the 8-KB limit for User-Defined Types (UDTs), allowing users to expand heir UDT size.


Remote SQL 2008 Management in sql server hosting
Customers can manage their SQL 2008 databases remotely with any sql client like SQL Management Studio or SQL Management Studio Express.


Web-based SQL Manager in sql server hosting
Customers can manage their SQL 2008 databases with a web-based SQL manager powered by myLittleAdmin.


Other SQL server Hosting 2008 & Control Panel Features

1. DBO Rights in the SQL Server Hosting
2. Stored procedure support for sql serce in the SQL Server Hosting
3. Full text search in the SQL Server Hosting
4. ASP.NET SQL Session in the SQL Server Hosting
5. Order Multiple SQL 2008 Database Addons in the SQL Server Hosting
6. SQL Disk Space Meter in the SQL Server Hosting
7. SQL Login Password Reset Tool in the SQL Server Hosting
8. Additional SQL 2008 Login Manager in the SQL Server Hosting


The goal of the SQL Server Hosting Toolkit is to enable a great experience around SQL Server in shared hosting environments.

The toolkit will eventually consist of a suite of tools and services that holsters can deploy for use by their customers. It will also serve as an incubation vehicle for tools that hosting customers can download and use directly, regardless of whether their holster has deployed the toolkit. See the Project Roadmap for details on where we're going.


How to Order a SQL Server Database Hosting Plan

If you wish to add a SQL Server database option to your hosting account, simply add the option when you order your hosting account. If you need a SQL Server database without a website, you can do it easily.

SQL Backup Service
SQL Backup Service is optional additional backup service that lets you determine how often you want accessible backup files created of your valuable information. See how you can go beyond emergency backups and be in control of your data protection.

SQL Server Hosting Technical Information
For information on using SQL Server Enterprise Manager, SQL Server Query Analyzer, DTS, ODBC/OLEDB database connections, SQL Server Management Studio and more,

SQL Server 2005 Information and Resources
SQL Server 2005 drives better decision-making with enterprise-grade reporting and data analysis. A highly reliable database platform for critical applications, SQL Server 2005 delivers high levels of availability, performance, and security. For more information and links to SQL Server 2005 resources.

Read More

SQL Server 2005 — Enhancements

What are the enhancements for Data Base Administrators in SQL Server 2005 ?

SQL Server 2005 has provide single management console that enables data base Administrators monitor, manage and tune all databases and services. SQL Management Object (SMO) is an efficient management infrastructure to easily program. SMO provides all the management functionalities of the SQL Server and is implemented as a Microsoft .NET Framework assembly.
The primary purpose of the SMO is to automate administrative tasks such as retrieving configuration settings, creating new databases, applying T-SQL scripts, Creating SQL Server Agent jobs and so on

1. SQL Server Management Studio :
It is a one point access to a number of services – the relational database, the Integration services, Analysis services, Reporting services, Notification Services and SQL Mobile.
Indexing capabilities
The SQL Server 2005 has been greatly enhanced. Indexes can be created, rebuilt or dropped online without disturbing existing indexes. This online indexing capability allows parallel processing, concurrent modifications to the table in the database or clustered index data or any other associated indexes.

2. Peer to peer model of replication
It is enables synchronization of transactions with an identical peer database. This further improves availability of data.

3. Table and index partitioning
It is concept of large databases by dividing the whole into manageable chunks. The concept is not new to the SQL Server, but the partitioning of tables horizontally across file groups in the database is new.

4. Snapshot isolation
This feature allows users access the last row that was committed by providing a transactional and consistent view of the database.

5. Replication monitor tool
It defines a new standard for managing complex data replication operations. Its interface is intuitive and has a number of data metrics that are useful.

6. New security model
Security model of the database platform now provides more precise and flexible control for ensuring security of store data.

7. Encryption capabilities
Data encryption of the database has been integrated with the management infrastructure for centralization of security assurance and server policy.

8. Secure computing measures
Those things have been put in place to enable deployment of a secure environment.
SQL Server Express Manager (XM)
This is a free Query Editor tool that is available for download.

9. Improved Business Intelligence

10. Improved Analysis Services

11. Improved Reporting services

Read More

Connect to Microsoft SQL server 2008 (enterprise) database from Visual C# 2008 Express

Question -

i've created a new database using SQL server managment studio with no problem.
database via Management Studio there is no problem, the database appears in the Explorer.

however, in Visual C# Express, when I try to 'Add Connection' as follows

Data source - MS SQL Server Database File
Database File Name - browse to appropriate folder and select
Log in using WIndows Authentification

And use the same database I receive the message (after a while):
"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

and yes i did detach the database / disable windows firewall / shutdown anti-virus sw before i try to connect.

* also, let me say i manage to connect for OLE DB (Access 2007) with no problems

does visual c# express edtion got to do with this problem, i mean if full version of visual studio can solve the problem ?

Looking for a solution if possible for the SQL server 2008.


Answer -

Upgrade will definitely solve the problem. The full version of Visual Studio provides support to SQL Server.
The comment about "Real" SQL Server, means that you can connect to the server itself (rather than just the SQL Server files)
I have heard of folks who just bypass the connection wizard and code the connection to the SQL Server "by hand". Using this technique, you *can* use C# Express to connect to a SQL Server... but you've got to do all the work.

Read More