Set up and connect to local SQLEXPRESS database using SQL Server Management Studio 2008 r2 and adodbapi

0
votes

For testing purposes, I am attempting to setup a SQL Server database on my local machine and connect to it using adodbapi in Python. I used SQL Server Management Studio 2008 r2 to create a database and table. I enabled the SQL Server Browser service, but I am still unable to connect using adodbapi. Using this connections string:

adodbapi.connect(r'Provider=SQLOLEDB;Data Source=COMPUTERNAME.\SQLEXPRESS;Initial Catalog=Test;User ID=userName; Password=password;')

I get the following error:

Traceback (most recent call last): File "", line 1, in File "C:\Python26\ArcGIS10.0\lib\site-packages\adodbapi\adodbapi.py", line 307, in connect raise OperationalError(e, "Error opening connection: " + connection_string) OperationalError: (com_error(-2147352567, 'Exception occurred.', (0, u'Microsoft OLE DB Provider for SQL Server', u'[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.', None, 0, -2147467259), None), 'Error opening connection: Provider=SQLOLEDB;Data Source=COMPUTERNAME.\SQLEXPRESS;Initial Catalog=Test;User ID=userName; Password=password;')

Any insight into what I'm missing?

1
pythonsql-serversql-server-2008ssmsadodbapi
Why do you have COMPUTERNAME.\SQLEXPRESS? Did you try COMPUTERNAME\SQLEXPRESS or .\SQLEXPRESS (one or the other, not both)? - Aaron Bertrand
I've been poking around for a few hours trying to find the answer and I came across a post that mentioned adding the dot before "\SQLEXPRESS". With the dot added, when I attempt to connect, it takes a minute to think like it's actually doing something. Without the dot, it just excepts stating that the server doesn't exist. From what I understand, the dot means local. - bluefoot
And with .\SQLEXPRESS do you get a different error message after a minute, or the same error message? Are you sure the SQLEXPRESS service is running? - Aaron Bertrand
I get the same error message, but instead of taking a minute to think it immediately excepts. I THINK the service is running... I started the SQL server browser service, but I'm very new to this so I may have missed a step. Apart from SQL Server Browser it says that SQL Server (SQLEXPRESS) is started. - bluefoot

1 Answers

0
votes

I did a little more digging around and I ended up haphazardly stumbling on the answer. I was missing "Integrated Security=SSPI" in my connection string and it turns out I didn't need the dot before "\SQLEXPRESS" in my data source. Here's the connection string that worked for me:

adodbapi.connect(r'Provider=SQLOLEDB;Data Source=COMPUTERNAME\SQLEXPRESS;Initial Catalog=Test;User ID=COMPUTERNAME\USERNAME; Password=PASSWORD;Integrated Security=SSPI')