0

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?

bluefoot
  • 189
  • 2
  • 11
  • Why do you have `COMPUTERNAME.\SQLEXPRESS`? Did you try `COMPUTERNAME\SQLEXPRESS` or `.\SQLEXPRESS` (one or the other, not *both*)? – Aaron Bertrand Nov 23 '12 at 21:20
  • 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 Nov 23 '12 at 21:23
  • 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 Nov 23 '12 at 21:25
  • 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 Nov 23 '12 at 21:26

1 Answers1

0

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')
bluefoot
  • 189
  • 2
  • 11
  • If you specify a username and password explicitly, you shouldn't need the `Integrated Security` line. Can you confirm that your connection string does not work when you name the server correctly and you leave that attribute out? – Aaron Bertrand Nov 23 '12 at 22:18
  • I just ran it again with both connection strings and it did not work without the integrated security property defined. – bluefoot Nov 23 '12 at 22:31