login failed : sql server 2008 R2

[Jonathan Cox]

Howdy. I'm a newbie trying to set up a mssql server and python code to
talk to it for development/learning. I'm running a sql server 2008 R2
server on my machine, and I simply want to use pyodbc (eventually,
sqlalchemy) to connect to it so that I can start learning database
manipulation. When I try to connect, I get an error:

code:
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client
10.0};SERVER=localhost;DATABASE=testdb;UID=guest')

Result:
Traceback (most recent call last):
File "<pyshell#18>", line 1, in <module>
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client
10.0};SERVER=localhost;DATABASE=testdb;UID=guest')
Error: ('28000', "[28000] [Microsoft][SQL Server Native Client 10.0]
[SQL Server]Login failed for user 'guest'. (18456)
(SQLDriverConnectW); [28000] [Microsoft][SQL Server Native Client 10.0]
[SQL Server]Login failed for user 'guest'. (18456)")

I've tried setting up another account in .\SQLExpress...\Security
\Logins that uses SQL Server Authentication and used this code to
connect:
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client
10.0};SERVER=localhost;DATABASE=testdb;UID=practiceuser;PWD=practiceuser')

But I get the same error. I realize that this is a nubbish problem,
and that its origin is probably in my sql server setup, but I'm
completely flummoxed here. Can anyone tell me what I'm doing wrong?

Many thanks.

[Jonathan Cox]

Apologies, but I should have added that this is all on a Win7 box. Box
is 64 bit, but python is a 32 bit installation.

[rms...@me.com]

Jonathan,

Are you getting a successful connection with your ODBC driver using
these credentials?

Mark Sharp

[Jonathan Cox]

And here I expose my extreme ignorance. I had thought that W7 came
with its ODBC drivers packaged to ready-to-use. I hadn't tried any
sort of connection with them at all.
I used the ODBC setup in Control Panel, and found that there was
nothing pointed at SQL Server. I created a new one, received an
affirmation that it was working when I clicked the 'test connection'
button, then tried the Py code again. Both:

cnxn = pyodbc.connect('DRIVER={SQL Server Native
Client10.0};SERVER=localhost;DATABASE=testdb;UID=guest')

#####################old code
and
cnxn =
pyodbc.connect('DRIVER={SQL_ODBC};SERVER=localhost;DATABASE=testdb;UID=guest')
################new code with the driver name that I just created

now produce this error message:
File "<pyshell#5>", line 1, in <module>
cnxn =
pyodbc.connect('DRIVER={SQL_ODBC};SERVER=localhost;DATABASE=testdb;UID=guest')
Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source
name not found and no default driver specified (0)
(SQLDriverConnectW)')

I have a feeling that I'm just not doing some very basic SQLServer/
ODBC setup correctly. Is there a rudimentary walkthrough or tutorial
that I can use? I feel bad using your time for elementary questions
that are likely unrelated to the workings of pyodbc itself.

many thanks

[rms...@me.com]

I decided to try to duplicate your situation as much as possible and
got the same results. Fortunately, I also found a solution. This is
what I entered for Python 2.7.1 and pyodbc 2.1.8 on Windows 7 with a
instance of SQL Server 2008 running on the same box.
>>> import pyodbc
>>> cnxn = pyodbc.connect('DRIVER=SQL Server Native Client 10.0;SERVER=FRANKIE-W7\MAC;DATABASE=animal;UID=msharp;PWD=far5thing')
>>> cursor = cnxn.cursor
>>> cursor.execute("select top 5 id, start_date, end_date from diet")
<pyodbc.Cursor object at 0x01DB5DE8>
>>> row = cursor.fetchone()
>>> if row:
... print row
...
(' 6200;, datetime.datetime(1991, 11, 1, 0, 0),
datetime.datetime(2002, 12, 19, 8, 0))
>>>

A bit of explaination: It looks like the DRIVER needs to be the name
that is in the Drivers tab of the ODBC Data Source Administrator
control panel that connects to SQL Server 2008. I have both "SQL
Server" and "SQL Server Native Client 10.0" listed, but the second one
works.

SERVER does not seem to like "localhost" on my machine either so I
used the server name (FRANKIE-W7) though you could probably use
127.0.0.1 and the named instance (MAC) to make up the SERVER
parameter. We never allow guest accounts so I have to use a real
account along with authentication.

I usually just create a ODBC Data Source Name (DSN) using an ODBC
Manager application and that is the only argument needed.

[Michael Kleehammer]

I believe the reason "localhost" doesn't work for rms is because the instance name is not the default MSSQL instance name.  It would be no different than if FRANKIE-W7 was used without the \MAC suffix.

A couple of ideas for Jonathon:

First, it looks like there is a space missing between Client and 10.0: {SQL Server Native Client10.0}.  Should be {SQL Server Native Client 10.0}

Second, I believe the native client libraries are not installed by default.  If you are not using any SQL 2008-specific features, you may get by using "DRIVER={SQL Server};server=localhost".

See http://code.google.com/p/pyodbc/wiki/ConnectionStrings for some examples.

[rms...@me.com]

Jonathan got his connection working on Friday evening. He had to have
a user and allow SQL Server authentication. He can best tell you what
he had to do on the server configuration. He did not need to change
the Python side of things.

[Jonathan Cox]

Yes, it (unsurprisingly) turned out that I just did not have something
configured correctly in SQL Server Management Studio. The server was
set only to accept Windows Authentication (which still does not seem
to work for me, but I am unconcerned about that); I simply had to
click the radio button to make it accept both Windows *and* SQL Server
authentication. I do, however, still have to use the full "DRIVER=SQL
Server Native Client 10.0".

[armnest...@yahoo.com]

hey can anybody help me ?

i always get this error message 

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

please help me