How does one load style.css into a theme?

Make sure you have the files named and labeled correctly and in the right place.

functions.php located @ mytheme/functions.php

<?php
/**
 * Theme Functions
 */
function theme_name_scripts() {
    wp_enqueue_style( 'style-name', get_stylesheet_uri() );
    wp_enqueue_style( 'style-name', get_stylesheet_uri() );

}
add_action( 'wp_enqueue_scripts', 'theme_name_scripts' );

style.css located @ mytheme/style.css

/*
Theme Name: Twenty Thirteen
Theme URI: http://wordpress.org/themes/twentythirteen
Author: the WordPress team
Author URI: http://wordpress.org/
Description: The 2013 theme for WordPress takes us back to the blog, featuring a full range of post formats, each displayed beautifully in their own unique way. Design details abound, starting with a vibrant color scheme and matching header images, beautiful typography and icons, and a flexible layout that looks great on any device, big or small.
Version: 1.0
License: GNU General Public License v2 or later
License URI: http://www.gnu.org/licenses/gpl-2.0.html
Tags: black, brown, orange, tan, white, yellow, light, one-column, two-columns, right-sidebar, flexible-width, custom-header, custom-menu, editor-style, featured-images, microformats, post-formats, rtl-language-support, sticky-post, translation-ready
Text Domain: twentythirteen

This theme, like WordPress, is licensed under the GPL.
Use it to make something cool, have fun, and share what you've learned with others.
*/

If you are creating this theme yourself it’s most likely spelled something wrong or you forgot something.

Make sure your template is getting the header if you are using a header.php:

<?php get_header(); ?> 

In your header or in the template you need to have:

<?php wp_head(); ?>

I’m guessing it’s one of the above since you said it works when you add the tag. If not though try changing the name of the css file and/or adding a later priority. If you have plugins installed one of them might be using the same “handle” for their plugins stylesheet.

So instead of:

    wp_enqueue_style( 'style-name', get_stylesheet_uri() );

Try something random like:

    wp_enqueue_style( 'style189304', get_stylesheet_uri() );