As I understand it, third normal form (3NF) basically means there should be exactly one key.
No. 2NF, 3NF, and Boyce Codd Normal Form (BCNF) deal with functional dependencies. A table in 2NF means there are no partial key dependencies where a non-key column is dependent on some proper subset of a multi-column key. Tables such as the one in our example are already in 2NF as each candidate key is a single column. A table in 3NF means every non-key column is also not functionally dependent on some other non-key column, and thus creating a transitive dependency. It does not matter if there are one or a hundred candidate keys. Actually it is BCNF, not 3NF, which is the "final" normal form with regard to functional dependancies. This is because a table can be in 3NF yet not be in BCNF as there could be multiple candidate keys which overlap. Thus, when we use the term 3NF to mean "fully normalized" with respect to functional dependencies, what we really mean is BCNF.
If a table with say an auto-increment id column also has a column known to be unique and not null, eg social security number, this other column could be used as the key.
Not only could it be, it must be if we want to ensure the data stored in the database remains consistent with the rules we have identified in the real world!
Ignoring practical/business issues (eg ecurity/privacy risk when passing around SSN as a key/FK), from a strictly schema design aspect, would such a table not be in 3NF because there are effectively 2 keys?
As explained above, whether or not the table is in 3NF (or more importantly BCNF) is orthogonal to how many candidate keys it contains.
Would the answer vary on whether there was a unique key on the other column? If so, why?
No, simply because determining if the table is or is not in 3NF has nothing to do with how many candidate keys it has. It instead has everything to do with ensuring all the non-key columns are fully functionally dependent on those candidate keys.
But this does bring up an interesting point. Note that a unique key when defined as a constraint in a DBMS is not the same as a unique identifier defined as a business rule in a conceptual business model. Perhaps in our world we always know the person's SSN and thus it serves as a candidate key for a person, and perhaps we also introduce a surrogate key in the logical schema we call Person Id. Our business model includes the rule stating that SSN is a unique identifier for a person in our world. This implies a functional dependency of all the descriptive attributes on this identity attribute. This rule does not change just because we either forgot to or chose not to inform the DBMS. This is precisely why it is vital the constraint be declared - so that the DBMS can ensure the data stored is consistent with the rules of the business model! If we didn't create that unique constraint on SSN we can now inadvertently create more than one row for the same person with the same SSN; each row having a different Person Id!
An excellent primer on these topics is Fabian Pascal's Practical Database Foundation Series and Chris Date's Database Design and Relational Theory, from which this answer is derived. While each paper of Fabian's is a must read, paper #1 (which clearly defines the difference between the conceptual, logical, and physical levels) and paper #4 (which clearly defines the various kinds of keys) specifically address this question. Likewise, Chris' entire book is a must read while Part II is the section devoted to normalization with respect to functional dependency.