求助：SAS连接SQL Server失败，驱动与代码问题排查指引

Let's break down your problem step by step to resolve the connection errors you're seeing.

1. First: Verify SAS/ACCESS Component Installation #

Looking at your proc product_status output, SAS/ACCESS Interface to Microsoft SQL Server is not listed—even though your proc setinit shows you have a valid license for it. This is a key red flag: the component isn't installed (or isn't properly registered in SAS).

Without this component, the sqlsvr engine won't be available in SAS. Similarly, the "ODBC engine cannot be found" error means SAS/ACCESS Interface to ODBC is also missing from your installation.

Fix: Install Missing SAS/ACCESS Components #

Run your SAS installation media, select "Modify" your existing installation, and add these components:

• SAS/ACCESS Interface to Microsoft SQL Server
• SAS/ACCESS Interface to ODBC (if you plan to use ODBC connections)

Since your license covers these, they should be available for installation.

2. Install Required SQL Server ODBC Driver #

Both the sqlsvr and ODBC SAS engines rely on a 64-bit SQL Server ODBC driver (your SAS is 64-bit, as shown by WX64_WKS). Download and install the latest version from Microsoft (e.g., ODBC Driver 17 or 18 for SQL Server)—make sure to grab the 64-bit installer.

3. Clarifying DSN vs. Server/Database Names #

Your question about DSNs: A DSN is a pre-configured connection profile you create in Windows' ODBC Data Source Manager, not just the server or database name. Here's how to set it up:

1. Open the ODBC Data Source Manager (64-bit) (search for it in Windows Start Menu—make sure it's the 64-bit version, not 32-bit).
2. Go to the System DSN tab, click "Add".
3. Select the SQL Server ODBC driver you installed, then follow the wizard:
   • Enter a name for the DSN (this is what you'll use in SAS, e.g., clientA)
   • Enter your SQL Server instance name (e.g., SERVER01\SQLEXPRESS or just SERVER01 if using the default instance)
   • Choose SQL Server authentication, enter your datareader username and password
   • Select the default database you want to connect to
   • Test the connection to confirm it works before saving

4. Correct Connection Code Examples #

Once the components and driver are set up, use these valid code snippets:

Option 1: SAS sqlsvr Engine (Recommended) #

This is SAS's native engine for SQL Server, offering better integration and performance. You can use either a DSN or direct connection string:

Using a Pre-configured DSN:

LIBNAME database1 sqlsvr USER=datareader PASSWORD=myspassword DATAsrc=clientA;

Using Direct Connection (No DSN Needed):

LIBNAME database1 sqlsvr USER=datareader PASSWORD=myspassword 
    SERVER='your_sql_server_name\instance' DATABASE='your_target_database';

Option 2: ODBC Engine #

If you prefer ODBC, use this once the SAS/ACCESS ODBC component is installed:

Using a Pre-configured DSN:

LIBNAME database1 ODBC DSN=clientA USER=datareader PASSWORD=myspassword;

Using Direct Connection String (No DSN Needed):

LIBNAME database1 ODBC CONNECTSTRING="Driver={ODBC Driver 17 for SQL Server};Server=your_server_name;Database=your_db;Uid=datareader;Pwd=myspassword";

Final Notes #

• Always ensure you're using 64-bit tools across the board: SAS, ODBC driver, and ODBC Data Source Manager. Mismatched bitness is a common hidden issue.
• If you still get errors after installing components, restart SAS to ensure the new engines are loaded.

内容的提问来源于stack exchange，提问作者user9218778