Saturday, 23 January 2016

Error trying to establish connection: Unable to Initialize: Invalid authorization specification

Thought I'd write this one up as the exact error message didn't appear on google.  Of course, the sharp eyed among you will quickly spot the missing security / authorisation info..


15         LIBNAME myLib OLEDB PROPERTIES=('initial catalog'=MyCat) DATASOURCE='MySvr' PROVIDER=SQLNCLIxx.x SCHEMA=dbo
15       ! connection=shared;
ERROR: Error trying to establish connection: Unable to Initialize: Invalid authorization specification

ERROR: Error in the LIBNAME statement.


The fix was to provide a valid connection string, eg as follows (in this particular case):

LIBNAME myLib OLEDB PROPERTIES=(
              'Integrated Security'=SSPI
              'Persist Security Info'=True
              'initial catalog'=MyCat)
  DATASOURCE='MySrv' PROVIDER=SQLNCLIxx.x 
  SCHEMA=dbo connection=shared;

Given that this libname statement was being built dynamically from metadata, it was necessary to stick those properties into the server definition, as follows:
  1. Open SMC
  2. Expand Server Manager
  3. Expand the relevant Server (as specified in the Library definition)
  4. In the properties of the underlying Connection, select the Options tab
  5. Select OLE DB Connection Information Options
  6. Stick in the relevant properties as per the libname statement, including brackets.