25 November 2004
24 November 2004
Now I am a MCAD! This time I am taking on '070-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition' with the help from Professional SQL Server 2000 Programming, by R, Viera. Persnally, I don't like the book. The concepts and practices in each chapter is very loose. It is at lack of structural way of presenting idea. And it will be better if Viera is not using overly verbal language and side-tracking jokes. It will be more painless to read if it can be more concise. I found using Sql Server Book On Line offers great help.
Anyway it covers everything and it is a seriously huge book. My day bag is much lighter now.
19 November 2004
The typical application scenario is an internet web application located in DMZ then submits data via ADO.NET, either in-process or though middle tier.
1. Use windows authentication.
1) The web server should not be in the trusted domain. Create a local NT user account ‘Fred’ with least privileges. This account will be used as the IIS anonymous access account for the web application.
2) Enable IIS anonymous access for the web application.
3) On the Sql Server, mirror the NT account ‘Fred’ created above. Set Sql Server security login mode to be ‘Windows Only’.
4) Create a SQL Server Login for ‘Fred’. Grant ‘Fred’ to ‘Public’, ‘Deny Data Reader’ and ‘Deny Data Writer’ access.
5) Allow application access data via Store Procedures only. Grant ‘Fred’ to all user- defined store procedures exec rights.
2.When you specify a server in an ADO.NET connection string, always use IP address instead of the server's DNS name to cut the overhead on DNS resolution.
3.Specify 'Application Name' in the connection string. It makes SQL Profiler can pick up the connections quite handy, or just for trouble-shooting.
4.Do not use SQL Server application roles, it turns off connection pooling.
5.When possible, use the ExecuteNonQuery method with SQLCommand objects, as this is the most efficient way to execute queries from ADO.NET. Use output parameters with SQLCommand objects if you need to retrieve just a few values, or a single data row, instead of using more expensive techniques, such as a SQLDataAdapter, a SQLDataReader, or a strongly typed DataSet.
6.Avoid using Transact-SQL cursor at all possible, try use correlated sub-query or temporary table instead.
17 November 2004
The default source control provider links to Visual Studio is VSS (of course!). Howerver, since I installed PVCS Dimesion client for a different project recently, the source control changed to be PVCS.
It is not in my interest to use PVCS for the .NET projects I am working on. However, there is no way you can change it back to VSS from Visual Studio(it is not the tools|option|source safe menu.)
The only way to change this is from registry:
Run regedit. Browse to 'HKEY_LOCAL_MACHINE\SOFTWARE\SourceCodeControlProvider\InstalledSCCProviders', it should be a list of all installed SC providers there. Copy the Data (path) of the intended used source control provider, then use it to replace data of 'HKEY_LOCAL_MACHINE\SOFTWARE\SourceCodeControlProvider\ProviderRegKey'.
Maybe there is a switch program around? Sure I am not the first one met this problem. I will do one if I have time. Many thanks to Nick A. to share this.
16 November 2004
Initiatives: MS Pattern and Practices recommendation: Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication:
Extranet Security and Access Security.
Scenario 1 SqlServer and Web Server are running on the same box
Likely be the home pc or standalone development environment-the configuration is quite simple:
1. IIS6 (Windows 2003 Server)
Follow this article to configurate IIS 6 access to SqlServer.
In my experiments, it shows that the IIS anonymous account setting in the web app's '(Virtual) Directory Security setting | Authentication and access control' and disable/enable integrate Windows authentication is not relevant- it will always be an IIS_WPG member account used to access SqlServer. You can run EXEC sp_who to find out which account is used.
The only exception to above is you specify <identity impersonate="true"/> in web.config. I tried to use IUSER_
2. IIS5 (Windows 2000 Server)
If running on IIS5(Windows 2000)it is IUSER_
Scenario 2 SqlServer and Web Server are running on two machines
In short, the MS recommended way is:
1)Don't put Web server on the trusted domain, but DMZ.
2)Create a NT account that impersonate as IIS anonymous account on web server.
3)Create the exact same account(username/password) on the Sql Server machine.
4)Grant access to this account.
How to use Trusted Connection when SQL server and web Server are on two separate machines has step by step guide on this.
Following vbs script shows credentials for iis anonymous user account
Set IIsObject = GetObject ("IIS://localhost/w3svc")
WScript.Echo "AnonymousUserName = " & IIsObject.Get("AnonymousUserName") & vbCrlf
WScript.Echo "AnonymousUserPass = " & IIsObject.Get("AnonymousUserPass") & vbCrlf
WScript.Echo "WAMUserName = " & IIsObject.Get("WAMUserName") & vbCrlf
WScript.Echo "WAMUserPass = " & IIsObject.Get("WAMUserPass")
Set IIsObject = Nothing