Connection string problem to application role

[Katy44]

I have a really strange problem and I hope I've chosen the correct forum.

I have two databases, on separate SQL Server 2000 servers. On each database i have an application role set up, called HPSImporter. I want to run stored procedures on the database through Access.

This was the first time I had used application roles, and my first attempt at a connection string was:

sqlConn.Open "User ID=HPSIMPORTER;PWD=blue;Initial Catalog=TestDatabase;Data Source=TestServerName"

Where 'blue' is the password I'd set up. This worked fine. There wasn't a login named HPSImporter, and HPSImporter wasn't a user on the database (as it was already the name of the role I wouldn't be allowed even if I tried).

I then tried to set this to access the live database - all I did was change the server name (and database name). The application role was set up on the new database in the same way and given the same password. I now get:

"Data source name not found and no defualt driver specifid"

If I add "Provider=SQLOLEDB.1;" to the connection string I am told "login failed for user HPSImporter"

If I change the username and password to sa and the sa password it then works fine.

Why are the two databases on the different servers acting so differently?!

 

[Katy44]

Ok I sorted this out. I set up a trace on the server that was apparently working, and found that the commands were being run as my NT login, not as HPSImporter at all!

I would be curious to know though how it managed to do that, but that explains what was happening, I think.

 

[Katy44]

Definitely. I could change it to:

sqlConn.Open "User ID=;PWD=;Initial Catalog=TestDatabase;Data Source=TestServerName"
and it worked fine. Does anyone know why?

 

[JamesLean]

Sounds like the data source is set up to use Windows Authentication rather than SQL Server. If so, it will just ignore the UID and PWD in the conn string and use your windows login.

--James

 

[Katy44]

It is mixed - but will it take my NT login if it can?

 

[JamesLean]

Sorry, I misread your conn string. I was thinking "Data Source" was referring to an ODBC DSN, rather than just the name of the server.

On second look, all connection strings I've ever seen/used have always specified the provider (SQLOLEDB). The fact that yours is not, coupled with the fact that when you add it it starts to use the UID/PWD specified, may mean this has something to do with it, and by default if you don't specify it uses Windows Authentication.

--James

 

[Katy44]

That would make sense, whenever I did get it to work with an actual login it always did need a provider. Thank you!