Unit tests should never connect to a database. By definition, they should test a single unit of code each (a method) in total isolation from the rest of your system. If they don't, then they are not a unit test.
Semantics aside, there are a myriad of reasons why this is beneficial:
- Tests run orders of magnitude faster
- Feedback loop becomes instant (<1s feedback for TDD, as an example)
- Tests can be run in parallel for build/deploy systems
- Tests don't need a database to be running (makes build much easier, or at least quicker)
Unit tests are a way to check your work. They should outline all of the scenarios for a given method, which typically means all of the different paths through a method. It is your specification that you are building to, similar to double-entry bookkeeping.
What you are describing is another type of automated test: an integration test. While they are also very important, ideally you will have much less of them. They should verify that a group of units integrate with each other properly.
So how do you test things with database access? All of your data access code should be in a specific layer, so your application code can interact with mockable services instead of the actual database. It shouldn't care whether those services are backed by any type of SQL database, in-memory test data, or even remote webservice data. That is not their concern.
Ideally (and this is very subjective), you want the bulk of your code covered by unit tests. This gives you confidence that each piece works independently. Once the pieces are built, you need to put them together. Example - when I hash the user's password, I should get this exact output.
Let's say that each component is made up of roughly 5 classes - you'd want to test all of the points of failure within them. This equates to a lot less tests just to ensure everything is wired properly. Example - test you can find the user from the database given a username/password.
Finally, you want some acceptance tests to actually ensure you are meeting business objectives. There are even less of these; they may ensure the application is running and does what it was built to do. Example - given this test data, I should be able to log in.
Think of these three types of tests as a pyramid. You need a lot of unit tests to support everything, and then you work your way up from there.