Can be both and/or can be used in N-tier architecture....

If your client app is accessing SQL Server directly, it can be considered as 2-tier app.

If your client app is accessing middle-tier such as MTS/COM+ and those middle tier components access SQL Server, it can be 3 tier.

If you use web browser to access your you web server that in turns accesses SQL Server, it will be 3 tier architecture...