Hello all,

I connect to SQL servers at work with at ease via ODBC. Servers are set up to accept our windows credentials ("With integrated Windows Authentication" checked in ODBC setup.)

I connect with Access, Excell, etc. with ease, but when I try with SPSS, It asks me to enter a password. I enter my network credentials, but even after that it fails to connect.

Any advice?

Thanks,

Deguza

Have you tried connecting to a local data source of the correct bitness (32 or 64 bit)?

What do you see under Security if you go to configure for the source?

If you look at the Connection String in, say, Excel, does it match the string in Statistics?

Might there be an "architecture mismatch", e.g., a 32-bit source with 64 bit Statistics?

Hello Jon,

Thanks for your answer.

After reading what you wrote, I looked into what I am doing again. Here is a summary:

• I did find the connection string within Excel.
• I cannot find any connection string within SPSS. I think it does not get to the stage where it creates one.
• All my programs (Excel, Access, etc) and our SQL servers and DBs to my knowledge are 64-bit.
• The "ODBC Data Source Administrator" (64-bit version) app on Windows 10 is what I use to create "User Data Sources." These data sources are marked as "32/64-bit."
• The new version of Excel seems to bypass these "User Data Sources" and displays tables and views when I use Data>From Other Sources >From SQL Server. It does not ask me a user name or password. It must be recognizing my credentials and figuring out that I am an authorized for the DBs.
• When I try to reach the same DBs via SPSS using File > Import Data > Database > New Query, SPSS starts the "Data Wizard" and displays the ODBC Data Sources, and at the bottom an "Add ODBC Data Source" button.
• Whether I use an existing data source or "add" a new one, SPSS prompts me to enter -- it should not ask me to do this-- a user name and a password. When I enter my credentials, it fails to accept them.

Any comments?

Thanks!

Deguza

I have had that mysterious password prompt sometimes in the past and was never clear about the reason. My impression is that recent versions of SPSS Statistics don't have this problem, but the diversity of ODBC sources makes this hard to be sure about.

MS Office currently seem to use a different, non-ODBC protocol for common sources, but you could experiment by trying to use actual ODBC for these sources. Maybe it would reveal something.

GET DATA TYPE=ODBC does support encryption, and you might want to try turning this off for the CONNECTION string.

Beyond that I can only suggest filing a support request to see what else might be known about this problem.

Thanks Jon. I am very new to SPSS. (I used it some four decades ago, but that does not count!) I guess I have to learn how to create a connection string.

Deguza

The CSR says this, but you might not be able to get that far. (The comment about MS Office doesn't reflect the availability of its 64-bit version, which MS used to recommend against.)

The CONNECT and SQL subcommands are both required, and SQL must be the last subcommand.

Example

GET DATA /TYPE=ODBC

/CONNECT=

'DSN=MS Access Database;DBQ=/examples/data/dm_demo.mdb;'+

'DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;'

/SQL = 'SELECT * FROM CombinedTable'.

Note: If you are running the Windows 64-bit version of IBM SPSS Statistics, you cannot read Excel,

Access, or dBASE database sources with GET DATA /TYPE=ODBC. The 32-bit ODBC drivers for these

products are not compatible. You can, however, read Excel files with GET DATA /TYPE=XLS.

CONNECT subcommand

The CONNECT subcommand identifies the database source. The recommended method for generating a

valid CONNECT specification is to initially use the Database Wizard and paste the resulting syntax to a

syntax window in the last step of the wizard. See the topic for more information.

• The entire connect string must be enclosed in quotation marks.

• For long connect strings, you can use multiple quoted strings on separate lines, using a plus sign (+) to

combine the quoted strings.

Connection Pooling

If you access the same database source multiple times in the same job, you can improve performance

with connection pooling. At the end of the quoted CONNECT string, add Pooling=true.

ENCRYPTED and UNENCRYPTED subcommands

UNENCRYPTED allows unencrypted passwords to be used in the CONNECT subcommand. The default is

ENCRYPTED.

Hey, do you still have this issue or have you solved it ?

SHAREit MX Player

Has anyone been able to use 64-bit driver successfully with SPSS28 (64-bit version)?  My System ODBC Data Source Administrator configuration/connection tests are all successful but SPSS begins the get data but then produces error:  An unknown error has terminated communication with the processor. The SPSS Statistics Processor is unavailable.  Please restart the application to recover this session. 

I am wondering if anyone in the community is using version28 needed to apply any patches or change settings for SPSS to grab data using ODBC.