Category Archives: SQL Server

Database Connectivity Test with UDL File

I bounced around between projects a lot last week.  What each project had in common was the need to validate at least one SQL connection.  Whether you have SQL tools like SSMS installed or not, this is a very easy task if you are aware of the UDL (Universal Data Link) files. 

Create a new file and name it anything as long as it has the .udl extension. Open the file, choose a provider:

image

Click Next >> or navigate to the Connection Tab to provide connection information.  Once you provide server and login credentials, the database list will populate.  At this point, you know the connection is valid. but go ahead and click the Test Connection button anyway.

image

On the final tab, you can provide extra connection information like Application Name which can come in handy

image

The All tab is beneficial if you want to build a valid connection string to include in your own applications.  If you save the file and then open in Notepad, you’ll find that said connection string:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=(local);Application Name=TestApp

I hope this tip helps save you some time.  How do you test if you don’t have SSMS installed?

Configure SQL Server to Allow Remote Connections

Okay. This post isn’t about configuring SQL to allow remote connections, but wait, I still may be able to help you out.

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The imageserver 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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)"

I love this exception. It summarized the issue and leads you down a path to solving the problem.  I do wish the bit about allowing remote connections was left out of the message though. I can’t think of a time when having remote connections disabled caused me grief.  Heck, I can’t ever remember how to enable remote connections unless I Google for the answer.

Anyway, 9 out of 10 times, SQL Server simply isn’t running.  That’s why the exception occurs.  The next time this exception pops up, open up the services console and make sure SQL Server is started.  And if that’s not the problem, only then start digging into the other possible reasons for the failure.

Getting Started with ASP.NET Membership, Profile and RoleManager

A new ASP.NET MVC project includes preconfigured Membership, Profile and RoleManager providers right out of the box.  Try it yourself – create a ASP.NET MVC application, crack open the web.config file and have a look. 

First, you’ll find the ApplicationServices database connection:

  1. <connectionStrings>
  2.   <add name="ApplicationServices"
  3.        connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"
  4.        providerName="System.Data.SqlClient"/>
  5. </connectionStrings>

 

Notice the connection string is referencing the aspnetdb.mdf database hosted by SQL Express and it’s using integrated security so it’ll just work for you without having to call out a specific database login or anything.

Scroll down the file a bit and you’ll find each of the three noted sections:

  1. <membership>
  2.   <providers>
  3.     <clear/>
  4.     <add name="AspNetSqlMembershipProvider"
  5.          type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
  6.          connectionStringName="ApplicationServices"
  7.          enablePasswordRetrieval="false"
  8.          enablePasswordReset="true"
  9.          requiresQuestionAndAnswer="false"
  10.          requiresUniqueEmail="false"
  11.          passwordFormat="Hashed"
  12.          maxInvalidPasswordAttempts="5"
  13.          minRequiredPasswordLength="6"
  14.          minRequiredNonalphanumericCharacters="0"
  15.          passwordAttemptWindow="10"
  16.          passwordStrengthRegularExpression=""
  17.          applicationName="/"
  18.             />
  19.   </providers>
  20. </membership>
  21.  
  22. <profile>
  23.   <providers>
  24.     <clear/>
  25.     <add name="AspNetSqlProfileProvider"
  26.          type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
  27.          connectionStringName="ApplicationServices"
  28.          applicationName="/"
  29.             />
  30.   </providers>
  31. </profile>
  32.  
  33. <roleManager enabled="false">
  34.   <providers>
  35.     <clear />
  36.     <add connectionStringName="ApplicationServices" applicationName="/" name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  37.     <add applicationName="/" name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  38.   </providers>
  39. </roleManager>

Really. It’s all there. Still don’t believe me.  Run the application, walk through the registration process and finally login and logout.  Completely functional – and you didn’t have to do a thing!

What else?  Well, you can manage your users via the Configuration Manager which is hiding in Visual Studio behind Projects > ASP.NET Configuration.

image

The ASP.NET Web Site Administration Tool isn’t MVC-specific (neither is the Membership, Profile or RoleManager stuff) but it’s neat and I hardly ever see anyone using it.  Here you can set up and edit users, roles, and set access permissions for your site. You can manage application settings, establish your SMTP settings, configure debugging and tracing, define default error page and even take your application offline.  The UI is rather plain-Jane but it works great.

image

And here’s the best of all.  Let’s say you, like most of us, don’t want to run your application on top of the aspnetdb.mdf database.  Let’s suppose you want to use your own database and you’d like to add the membership stuff to it.  Well, that’s easy enough. Take a look inside your [drive:]\%windir%\Microsoft.Net\Framework\v2.0.50727\ folder.  Here you’ll find a bunch of files.  If you were to run the InstallCommon.sql, InstallMembership.sql, InstallRoles.sql and InstallProfile.sql files against the database of your choices, you’d be installing the same membership, profile and role artifacts which are found in the aspnet.db to your own database. 

Too much trouble?  Okay. Run [drive:]\%windir%\Microsoft.Net\Framework\v2.0.50727\aspnet_regsql.exe from the command line instead.  This will launch the ASP.NET SQL Server Setup Wizard which walks you through the installation of those same database objects into the new or existing database of your choice. You may not always have the luxury of using this tool on your destination server, but you should use it whenever you can. 

image

Last tip: don’t forget to update the ApplicationServices connectionstring to point to your custom database after the setup is complete.

At the risk of sounding like a smarty, everything I’ve mentioned in this post has been around for quite a while. The thing is that not everyone has had the opportunity to use it.  And it makes sense. I know I’ve worked on projects which used custom membership services.  Why bother with the out-of-the-box stuff, right?   And the .NET framework is so massive, who can know it all. Well, eventually you might have a chance to architect your own solution using any implementation you’d like or you will have the time to play around with another aspect of the framework.  When you do, think back to this post.

Issue Creating SQL Login for AppPoolIdentity on Windows Server 2008

IIS7 introduced the option to run your application pool as AppPoolIdentity. With the release of IIS7.5, AppPoolIdentity was promoted to the default option.  You see this change if you’re running Windows 7 or Windows Server 2008 R2.  image

On my Windows 7 machine, I’m able to define my Application Pool Identity and then create an associated database login via the SQL Server Management Studio interface.  No problem.  However, I ran into some troubles when recently installing my web application onto a Windows Server 2008 R2 64-bit machine.  Strange, but the same approach failed as SSMS couldn’t find the AppPoolIdentity user.  Instead of using the tools, I created and executed the login via script and it worked fine. 

Here’s the script, based off of the DefaultAppPool identity, if the same happens to you:

CREATE LOGIN [IIS APPPOOL\DefaultAppPool]
FROM WINDOWS WITH DEFAULT_DATABASE=[master]
USE [Chinook]
CREATE USER [IIS APPPOOL\DefaultAppPool] FOR LOGIN [IIS APPPOOL\DefaultAppPool]

Keystrokes to Set Null Value in SSMS

Let’s say you are viewing results in Enterprise Manager or the SQL Server Management Tools and you really want to update a cell will a NULL value.  Sure you could script it.  Heck, you’re welcome to wrap the one line script in a Transaction and produce a rollback script as well if you’d like.  But, if you are lazy like me, you just want to update the value using tools, all reckless-like.  The trick is to select into the cell and press the lucky CTRL + 0 key combo. That’s a zero, by the way. I also go for CTRL + DEL, and then wonder why NULL doesn’t magically appear.  Hence the post. 

image

Upgrade to SQL Server 2008 with SQL Server Management Studio (SSMS) 2008

I have installed SQL Server 2008 and SQL Server Management Studio 2008 a number of times and it has always been a snap*..until yesterday.

As it turns out, previous installations were always applied to machines (mostly VMs) which weren’t running early versions of the SQL Server and associated tools.  Yesterday, I performed my first upgrade from SQL Server 2008 and SQL Server Management Studio 2005 and it took a painfully long time to get everything in place.

There seems to be general discontent with the SQL Server 2008 installation/upgrade experience – particularly when it comes to SQL Server Management Studio. After yesterday’s debacle, I can understand why.  In retrospect, an upgrade to 2008 is simple if you follow the right steps.  If not, you’re bound to get frustrated and waste a couple of hours. 

If I knew then what I know now…

  • You cannot manage SQL Server 2008 with 2005 Management Tools. Go ahead and try it. SQL Server Management Studio 2005 will bark at you.
  • A stand-alone SQL Server Management Studio 2008 installer does not exist.  The Management Tools must be installed as a features available via the main SQL Server2008 installer. The Microsoft download for SQL Server Management Studio 2008, as far as I am concern is a hoax.  It is merely the SQL Server 2008 installer given a different file name. Seriously.
  • You cannot run SQL Server Management Studio 2005 and SQL Server Management Studio 2008 side by side. An attempt to install SSMS 2008 will fail if you have yet to uninstall SSMS 2005.

Save yourself some trouble.  Before kicking off your SQL Server 2008 / SQL Server Management Studio 2008 installation or upgrade, uninstall SQL Server Management Studio 2005.  If you imagedon’t, you will be able to successfully install SQL Server 2008, but the Management Tools installation will fail (gracefully.) If, per chance, you find yourself in this failure condition, continue with the SQL Server Management Studio 2005 uninstall, rerun the SQL Server 2008 installer and reattempt the Management Tools installation.  This can be done by following the “New SQL Server stand-alone installation of add features to an existing installation” option under the Installation view.  This time around select the Management Tools when the available features are presented and you should be all set.  You may find the management tools take a surprisingly long time to install, but, believe me, this is nothing compared to the time you might waste if you didn’t know the right steps to follow in the first place.

* Relatively standard installation instructions which work consistently well for me:

  • Run Setup.exe
  • Installation > Select “New SQL Server stand-alone installation or add features to an existing installation.”
  • Select the following Features:
    • Database Engine Services
    • Reporting Services
    • Client Tools Connectivity
    • Management Tools – Basic (If SSMS 2005 isn’t installed)
    • Management Tools – Complete (If SSMS 2005 isn’t installed)
  • Select the Named Instance/Instance ID based on established naming convention. For example, BGHOMESQL01
  • Set the following Service Startup Types:
    • SQL Server Agent – Automatic
    • SQL Server Database Engine – Automatic
    • SQL Server Reporting Services – Automatic
  • Use the same account for all SQL Server Services and provide account. On local machine, I suggest using the Current User.
  • Set the Authentication Mode:
    • Mixed Mode (SQL Server Authentication and Windows Authentication)
  • Provide the built-in SQL Server admin account. On local machine, I suggest using the Current User.
  • Configure Reporting Services:
    • Install the native mode default configuration

SQL Tip: Uniquely Name Table Columns

It is easy to see the subtle difference between the Product Tables below.  The table to the left adds the table name as a prefix to nearly all of the column names. image The table on the right represents very simple column names with no redundancy.  Early in my career, I preferred the implementation to the right as I felt this pattern best represented the object (table) itself.  After all, we all have a name.  We don’t have a PersonName. Well, it didn’t take me long to change my opinion.

The next time you are designing a database, do yourself a favor and mimic the pattern to the left.  Always prefix column names with the table name if the column name is likely to be repeated  in more than one table.  ID, Name, Description, DateAdd and DateChange are good examples of column names which tend to show up in multiple places.  When it comes time to build your queries and work with the result sets, you will thank me. 

Consider the two following routines — each returns the same result but the latter is much cleaner, readable, consists of less code and is inherently greater resistant to stupid coding errors especially within the syntax of the join and the column aliases.

-- Poor implementation
declare @Product table
(
    ID int, Name varchar(50)
)

declare @ProductVersion table
(
    ID int, ProductID int, VersionNumber int
)

insert into @Product(ID, Name)
select 1, 'product 1'
union
select 2, 'product 2'

insert into @ProductVersion (ID, ProductID, VersionNumber)
select 1, 1, 1
union
select 1, 2, 1

-- Note the column aliases and
-- join on less-than-obvious columns
select P.ID as ProductID, PV.ID as ProductVersionID from
@Product P inner join
@ProductVersion PV on (P.ID = PV.ProductID)

go

-- Proper implementation
declare @Product table
(
    ProductID int, Name varchar(50)
)

declare @ProductVersion table
(
    ProductVersionID int, ProductID int, VersionNumber int
)

insert into @Product(ProductID, Name)
select 1, 'product 1'
union
select 2, 'product 2'

insert into @ProductVersion (ProductVersionID, ProductID, VersionNumber)
select 1, 1, 1
union
select 1, 2, 1

-- Note there's no need for aliases and
-- the join obvious
select P.ProductID, PV.ProductVersionID from
@Product P inner join
@ProductVersion PV on (P.ProductID = PV.ProductID)

Keep this tip in mind.  It will save you heartache throughout your next project.

Code Generation with Stored Procedures?

I very recently posted about using SubSonic to generate my DAL and SSMS Tool Pack to generate the complementing stored procedures.  In response to the post, Jon Galloway asked a great question in the comments:

Thanks for the pointer to SSMS Tool Packs. It looks really interesting. One thing I’m having trouble picturing is the overlap between the two [SubSonic and SSMS Tool Pack]. Once you have a DAL, what do you need CRUD routines for? Bulk operations, or something that’s a result of the existing architecture?

For those familiar with SubSonic you can appreciate Jon’s question.  Since SubSonic’s generated DAL provides you with CRUD methods and easily allows you build resulting parameterized queries, you are no longer dependent upon having compiled routines sitting in the database. 

Almost on queue, Caffeinated Coder recently a great article on why one should “Just Say No to Manual CRUD” which provide a list of resources which present good counter arguments against the conventional stored proc wisdom.

So, why the heck am I still using stored procedures?

If I were to be honest, the number one reason I’m sticking with SPs is they are familiar and they provide me with a sense of comfort.  Most of my uneasiness with “embedding” data access into the application code is tied to deployment and maintenance.  For example, I like to have multiple “outs” when it comes to rollbacks and I like to keep emergency fixes as isolated as possible. Since stored procedures could be considered more atomic than even the most lightweight DLL, I can update a live application by altering a single stored procedure with more confidence than copying/replacing application file(s.)  Assuming you have a single database and a web application running on multiple web servers, in my opinion, the stored procedure update is best solution since time to implement is low, risk is minimal and downtime is eliminated. 

I had a good follow up conversation with Jon about all of this last week and the bottom-line is that one needs to put the right architecture in place based on their coding, deployment and maintenance needs.  I am currently sticking with stored procedures but this approach isn’t necessarily right for everyone.