Problem Note 43139: A connection error occurs when you update metadata for DBMS tables and the database user ID or password is stored in metadata as a macro variable
 |  |  |  |
Connection errors can occur if you store a DBMS user ID or password value in your SAS® Metadata Repository as a macro variable reference instead of the hardcoded value. In such a situation, if you use the METALIB procedure to update SAS metadata for tables in a library that require those DBMS credentials, then PROC METALIB fails to connect to the database.
The text of the error depends on the database to which you attempt to connect. If you attempt to connect to an Oracle library, the following error message appears:
ERROR 3-75: ORACLE connection error:
For a DB2 library, the following error message appears:
ERROR: CLI error trying to establish connection: [IBM][CLI Driver] SQL30082N Security processing failed with reason "24"
("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001
When PROC METALIB generates the DBMS LIBNAME statement in order to connect to the underlying database, it embeds the user ID and password values in single quotation marks. However, SAS® software only resolves macro references that are enclosed in double quotation marks.
The only circumvention is to remove the macro variable reference from SAS metadata and replace it with the user's actual user ID or password value.
Note: The ability to enter a user ID as a macro variable is being considered for a future release.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | Base SAS | Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| z/OS | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Microsoft Windows XP 64-bit Edition | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Microsoft® Windows® for x64 | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 for x64 | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2008 | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2008 for x64 | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Microsoft Windows XP Professional | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Windows 7 Enterprise 32 bit | 9.21_M3 | 9.2 TS2M3 | ||||
| Windows 7 Enterprise x64 | 9.21_M3 | 9.2 TS2M3 | ||||
| Windows 7 Home Premium 32 bit | 9.21_M3 | 9.2 TS2M3 | ||||
| Windows 7 Home Premium x64 | 9.21_M3 | 9.2 TS2M3 | ||||
| Windows 7 Professional 32 bit | 9.21_M3 | 9.2 TS2M3 | ||||
| Windows 7 Professional x64 | 9.21_M3 | 9.2 TS2M3 | ||||
| Windows 7 Ultimate 32 bit | 9.21_M3 | 9.2 TS2M3 | ||||
| Windows 7 Ultimate x64 | 9.21_M3 | 9.2 TS2M3 | ||||
| Windows Vista | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Windows Vista for x64 | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| 64-bit Enabled AIX | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| 64-bit Enabled HP-UX | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| 64-bit Enabled Solaris | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| HP-UX IPF | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Linux | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Linux for x64 | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| OpenVMS on HP Integrity | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
| Solaris for x64 | 9.21_M3 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 | ||
PROC METALIB passes the database credentials to the DBMS using single quotation marks, which prevents the macro variable from resolving.
| Type: | Problem Note |
| Priority: | medium |
| Topic: | Data Management ==> Data Sources ==> External Databases Data Management ==> Data Sources ==> External Databases ==> Adabas Data Management ==> Data Sources ==> External Databases ==> AsterData Data Management ==> Data Sources ==> External Databases ==> Datacom Data Management ==> Data Sources ==> External Databases ==> DB2 Data Management ==> Data Sources ==> External Databases ==> Dbase Data Management ==> Data Sources ==> External Databases ==> GreenPlum Data Management ==> Data Sources ==> External Databases ==> HP Neoview Data Management ==> Data Sources ==> External Databases ==> IDMS Data Management ==> Data Sources ==> External Databases ==> IMS Data Management ==> Data Sources ==> External Databases ==> Informix Data Management ==> Data Sources ==> External Databases ==> Ingres Data Management ==> Data Sources ==> External Databases ==> Lotus Data Management ==> Data Sources ==> External Databases ==> MS Access Data Management ==> Data Sources ==> External Databases ==> MS Excel Data Management ==> Data Sources ==> External Databases ==> MS SQL Server Data Management ==> Data Sources ==> External Databases ==> MySQL Data Management ==> Data Sources ==> External Databases ==> Netezza Data Management ==> Data Sources ==> External Databases ==> ODBC (Open Database Connectivity) Data Management ==> Data Sources ==> External Databases ==> OLEDB Data Management ==> Data Sources ==> External Databases ==> Oracle Data Management ==> Data Sources ==> External Databases ==> Oracle RDB Data Management ==> Data Sources ==> External Databases ==> Redbrick Data Management ==> Data Sources ==> External Databases ==> SPSS Data Management ==> Data Sources ==> External Databases ==> Sybase Data Management ==> Data Sources ==> External Databases ==> Sybase IQ Data Management ==> Data Sources ==> External Databases ==> System 2000 Data Management ==> Data Sources ==> External Databases ==> Teradata Data Management ==> Data Sources ==> Metadata |
| Date Modified: | 2012-10-02 09:15:58 |
| Date Created: | 2011-05-03 09:58:21 |