connecting to MSSQL DB using SQLAlchemy throwing error type 08001

Question

I've got this code:

from sqlalchemy.engine import create_engine
engine = create_engine('mssql+pyodbc://sa:[email protected]/master.db', echo=True)
connection = engine.connect()
connection.execute(
    """
    CREATE TABLE users (
        username VARCHAR PRIMARY KEY,
        password VARCHAR NOT NULL
    );
    """
)
connection.execute(
    """
    INSERT INTO users (username, password) VALUES (?, ?);
    """,
    "foo", "bar"
    )
result = connection.execute("SELECT username FROM users")
for row in result:
    print "username:", row['username']
connection.close()

I'm getting this error:

sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53)') None None

I ran this query on my DB

select db_name()

and got this O/P

1. master

Can someone explain what is wrong in my logic?

Your connection URI uses the database name master.db. Is this the correct name of your database? Do you want to connect to SQL server's master database (this is probably not a good idea, since it is an internal database for SQL server). — codeape
Also test your connection info (i.e. server IP, username and password) from another client, for instance SQL server management studio. — codeape
OK, but the db name is master not master.db as in your db URI. — codeape
Have you checked that 1) Your SQL server is listening for TCP/IP connections 2) SQL server logins are enabled ? — codeape

Hemant Hemant · Accepted Answer · 2013-09-25T05:52:51

I found the issue, the firewall on the server was rejecting my connection. I disabled the Firewall to check the connection and I got the connection. I've to make an exception in the firewall rule to allow the connection to the SQL Server TCP/IP Port.

One can refer to this link for steps to do so http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

connecting to MSSQL DB using SQLAlchemy throwing error type 08001

1 Answers