Nilas Jensen's Bookmarks tagged sql_server →  View Popular

Granting Access to a Remote SQL Server

If you are accessing a database on another server in the same domain (or in a trusted domain), the Network Service account's network credentials are used to authenticate to the database. The Network Service account's credentials are of the form DomainName\AspNetServer$, where DomainName is the domain of the ASP.NET server and AspNetServer is your Web server name.

For example, if your ASP.NET application runs on a server named SVR1 in the domain CONTOSO, the SQL Server sees a database access request from CONTOSO\SVR1$.

To access a remote SQL Server using Network Service

To grant access to a remote database server in the same domain or a trusted domain, follow the steps described earlier for a local database, except in step 4, use the DomainName\AspNetServer$ account to create the database login.

Note   In production environments, you should place the network service account into a Windows group and create a SQL Server login for the Windows group.

   Index a MAC of your data

   We can use a MAC (Message Authentication Code) of the plaintext to create a new indexing column. This approach is similar to using a hash, but it requires a secret key to calculate the MAC. This prevents an unauthorized user to use a general purpose dictionary of hashed values and it will also prevent her from creating a targeted dictionary without having access to the MAC key. Please note that an attacker who has access to the MAC key can generate a targeted dictionary.

  SQL Server 2005 doesn’t provide a function for computing a MAC, but it is possible to write a user-defined function that calculates a MAC using either SQL Server 2005 CLR or by reusing the existing builtin functions.

3) How to prevent a symmetric key loss

Because there is no direct way to individually backup and restore a symmetric key, what can we do to protect these keys? One solution is to do frequent database backups. Because the keys are stored in the database, they will be saved with the database. There is an additional solution, however, which involves deriving a symmetric key from a passphrase. The same key can always be recreated as long as the same passphrase is used. To create a key derived from a passphrase, we can use the KEY_SOURCE clause of CREATE SYMMETRIC KEY. While KEY_SOURCE will allow us to regenerate the same key, to be able to decrypt data encrypted by a previous incarnation of the key, we should also generate the key with the same key_guid value; for this, we must use the IDENTITY_VALUE clause. Together, the KEY_SOURCE and IDENTITY_VALUE clauses provide a way to recreate the same key with the same key_guid identifier.

How is this done? Well, to start with, ADO.NET adds a couple of new keywords to the ConnectionString. The ConnectionString is the set of parameters passed to ADO that tells it how to find and access the target data source, as well as identify the user. It also contains settings for a number of options, including how long to wait for the connection to complete and, in this case, the name of the user database to attach. This keyword (Attach-DBFilename) points to a user .MDF file which contains the preconfigured and prepopulated SQL Server database. When the ADO Open method sees this keyword, it tells SQL Server to attach this file and install it as a new databaseat least the first time. Each subsequent time the application runs, SQL Server determines if this is the same file it attached the first time and simply opens the existing database. When you use the default AutoClose option, SQL Server automatically disconnects from the data file when the last user closes his connection. This permits an uninstall application to delete the file and not disturb SQL Server.

Another ConnectionString option is used to create an entirely separate instance of the server. By specifying “User Instance=True” along with the “AttachDBFilename=