January 2006 Archives
Impersonating another account for XP and W2K is not that hard there's two ways to do it simply.
a) In web.config: <identity impersonate="true" user="" password"" />.
b) Change the anonymous account on the IIS server and in web.config set: <identity impersonate="true" />
For W2k3 you set up a new application pool with the new account as it's identity.
January 20, 2006 • Vol.28 Issue 3
Page(s) 24 in print issue
As a developer and author, I find myself constantly researching new technology. Ostensibly, my readers (mostly other developers) are doing the same. The problem is that the information available is all too often overwhelming—even for the experts. Incidentally, all of us are experts on something. Some know every aspect of getting an aging lawnmower to start; others (supposedly) know how to build an effective antiballistic missile defense system (although IMHO there are too many “experts” in this field). But I digress.
Updated: March 7, 2006: Added ASP.NET connecting info.
In the newsgroups and in my conference sessions, there still seem to be a lot of questions on getting connected. Perhaps this checklist can help. Yes, connecting can be a ... well, problematic. ASP apps also pose additional issues. In order to connect to SQL Server several factors must be in place. Go through this checklist to help get (and stay connected) to an instance of SQL Server.
1) Your application must identify the machine name and the instance of the target SQL Server. This means you’ll need to reference the machine name and the instance name unless you have installed SQL Server as the “default” SQL Server (the only option pre-SQL Server 2000). The syntax is “<machine name>\<instance name>”.
2) To identify the local machine name, you can use “.” or “(local)” or in VS 2005 (2.0 Framework) you can use My.Computer.Name. You can also define an alias to address a specific machine and instance and use this alias in the Server= element in the connection string. The term “localhost” refers to the local TCP/IP address and is used to access the local IIS server, not the local SQL Server.
3) When connecting to an instance of SQL Server directly via an IP address (which raises a number of scary security issues), see the addendum at the bottom of this checklist.
4) The DNS must be able to identify the named machine (it must resolve to a valid IP address). If the DNS is not working, the server can't be found. It might be necessary to alter the “hosts” file (Windows\system32\drivers\etc) to map a specific machine name to an IP address to circumvent this issue. DNS accessibility can be a function of how the NIC is configured or the availability (state) of the domain controller.
5) The targeted instance service must be running on the specified machine. SQL Server can be configured in single-user or paused mode or the service might be stopped for some reason. These states don't permit external connections. You can check the status of the service using the services.msc MMC snapin or via code (2.0 Framework).
6) If you're using SQL Server authentication, the server instance must be configured for mixed mode security. By, default SQL Server is not configured to support SQL Server authentication (what’s called “mixed-mode” security.
7) If you’re using SSPI authentication (Trusted Connection=True or Integrated Security=True (or SSPI)), the Windows account you’re using (or impersonating) must have an account on the server one way or another. This means there must be a SQL Server login that grants sufficient rights to the user’s group. By default, Windows users in the Administrators group are granted access to all objects but the DBA might (and usually does) restrict those rights.
8) In the ASP case, IIS opens the connection as ASPNET (pre-Windows 2003) so this login must be granted rights. Yes, you can try to impersonate a Windows user using an ASP application, but this is complex to setup and keep working. Other complicating factors here include running the SQL Server on a different system—other than the IIS system.
9) ASP applications might have to run under a trusted domain account.
10) The login account must exist and be granted rights to the initial catalog.
11) Depending on the protocol you choose (or is chosen for you), you must punch a hole or two in the client firewall if the server is on another machine. This means getting into the Windows firewall program. If the server is on the local system, it's best to bypass the network providers and use the shared memory provider. You must also make sure the protocols you need are enabled on the server--they are off by default. The best way to manage the services is using the new SQL Server Surface Area Configuration tool and the SQL Server Configuration Manager.
12) The server itself must have available connections. This is not a given. Check the server configuration to ensure that the license restriction or the DBA-imposed restriction on accounts has not been exceeded.
13) The connection pool must also have available space. The new ADO.NET 2.0 exception handlers are not that helpful. They often return a generic message that doesn't really help. The ConnectionString parser in ADO.NET 2.0 is better than ever. It’s more tolerant of additional spaces and such and will simply report an exception if the syntax is incorrect. That said, remember that the ConnectionString is used to test for existing connections in the connection pool. It can’t vary from connection-to-connection without creating a new pool. Connection pooling issues are not a concern for Windows forms applications (smart clients), but are a vital issue for ASP and Web Service applications.
14) For Windows 2000 and XP IIS logs on using “ASPNET” account credentials. For Windows 2003, IIS uses “INetworkService” by default. Some folks think that impersonating another account for XP and Windows 2000 is not that hard. They say there are two ways to do it:
a) In web.config: .
b) Change the anonymous account on the IIS server and in web.config set:
For Windows 2003 you must setup a new application pool with the new account as its identity. (Thanks to Patrik Löwendahl [C# MVP] for this contribution.)
15) When connecting to a SQL Server instance connected via IP address:
a. While 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server, you should still determine the port being exposed by the targeted server instance—each instance can be addressed by its own TCP port. The SQL Server Configuration Manager exposes the IP address of the TCP port if it has been assigned. If Dynamic Ports are enabled, then you need to determine that port. See KBs 269882, 823938 and 287932 for more details.
b. Using the same tool, make sure the TCP/IP protocol is enabled.
c. Using your Firewall administration tool on the client and the server, make sure this port is not blocked. It will be blocked by default.
d. Make sure your connection string uses “Network=dbmssocn;” as the protocol.
e. It might be easier to simply add an alias to the Host file located at “C:\WINDOWS\system32\drivers\etc”.
I think that covers most of the known issues. If you think I need to add more to the list, let me know.
Other useful links:
Its funny how just one employee can screw it up for an entire company.
Once when arriving in Sydney late they held a plane for us to go to Hobart and the gate agent went byond the call to help us.
I wrote a thank you letter to Qantas and they wrote back about how they trained their people and expect no less than "Perfect".
Then on a flight from Melbourne to Hobart a male flight atendant admonished me for having my digital camera on.
Then I had stuck it in the seat back and the light was still on -on its way to automatic shut off and he came by again and threatend to throw us off the plane [he may have been haveing his period so I left it alone.
I'm a retired electronic nuclear engineer and I guess I missed that class as digital cameras were not yet invented.
I thought I held the record for complaing but you have me beat [thank God] whoops theres the G word again.
Aloha from Maui [originaly from Duluth MN]
hardware of this phone is lower than mpx220.
While there are some special circumstances where SCOPE_IDENTITY() isn't the best approach
What are these special circumstances? Can you please elaborate. Thanks!
January 6, 2006 • Vol.28 Issue 1
Page(s) 23 in print issue
Starting the New Year right means making (and hopefully keeping) resolutions that will make us better people (or perhaps just thinner). I think about how I can be a better person all the time but I (like others, I suspect) don’t have the willpower to alter my self-destructive behavior enough to stop eating burgers.