How do Data Scientists integrate Predictive Modeling with SQL? I've heard a lot about the importance of knowing SQL but how do you guys at the larger firms integrate your software for modeling/analysis with the server that holds the data?

Do you simply access some SQL Workbench and download a .csv file then read the file in your software? Or do you gain access to the server directly through your software?

This question is coming from the guy who learned SQL but never learned how to synchronize that with R.
There is no need to download data in CSV format, in most cases it is actually bad practice. Consider cases where data size > 1GB and is updated daily. This would add a considerable overhead and is not easy to automate.

Instead you can take a look at various R packages that exist to fetch data from your SQL database. Using RODBC, SQLite, sqldf or other available libraries you can import data to R, run queries, create tables, update tables and pretty much anything else you will need.