Microsoft SQL 2008 Express

[kking124]

I am having an issue connecting an MSSQL 2008 Express Server configured in Windows Authentication Mode using the LabView database connectivity toolkit. I have tried the following connection strings and received the following errors:

DRIVER=SQL Server; SERVER=<machine name>\SQLEXPRESS; UID=<domain>\<user>; PWD=<password>;

the errors states:

NI_Database_API.lvlib:DB Tools Open Connec (String).vi->test odbc.vi<ERR>Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '<domain>\<user>'. in NI_Database_API.lvlib:DB Tools Open Connec (String).vi->test odbc.vi

PROVIDER=SQLOLEDB.1; SERVER=<machine name>\SQLEXPRESS; UID=<domain>\<user>; PWD=<password>;

the errors states:

NI_Database_API.lvlib:DB Tools Open Connec (String).vi->test odbc.vi<ERR>Exception occured in Microsoft OLE DB Provider for SQL Server: Login failed for user '<domain>\<user>'. in NI_Database_API.lvlib:DB Tools Open Connec (String).vi->test odbc.vi

I know this user has rights to modify the database and I know that it's not a firewall issue as I tested with the firewall off.

Help would be appreciated!

[kking124 · Accepted solution]

It appears that, because I am using Windows Authentication Mode, there is no need to use UID and PWD in the connection string; in fact, it's use is causing the error.

DRIVER=SQL Server; SERVER=<machine name>\SQLEXPRESS;

DRIVER=SQL Server; SERVER=.\SQLEXPRESS;

Can be used when connecting to an SQL 2008 Express Server.

[smercurio_fc]

Tip: If you don't already know, www.connectionstrings.com is a great site for getting connection strings. \

Message Edited by smercurio_fc on 05-27-2009 01:17 PM

[kking124]

Thanks for the tip.

Do you happen to know what the difference is between using a PROVIDER over a DRIVER?

[smercurio_fc]

One is used if you're connecting using OLE DB (Provider), and the other if you're connecting using ODBC (Driver).

[kking124]

Thank you, but I am asking about performance/code size tradeoffs between OLE DB vs ODBC - I know that they are different, but does it really matter which one I use in LabView? In the end, isn't OLE DB just implementing an ODBC connection to the data store anyway in the case of T-SQL databases?

Message Edited by kking124 on 05-28-2009 08:01 AM

[smercurio_fc]

Both OLE DB and ODBC are kind of "low-level" so they're both intended to be fast. ODBC was basically geared towards relational data stores. OLE DB wasn't really geared towards a specific data store so it can accomodate a variety of data stores. From what I've read OLE DB was originally intended as an abstraction layer built on top of ODBC. Some argue that ODBC isn't as fast as OLE DB in some cases, and others argue the opposite.You can Google for "ODBC vs OLE DB" for some fun reading over lunchtime.

As far as LabVIEW is concerned you would actually be probably using ADO anyway in terms of the programming. The Database Toolkit uses ADO, as does the free LabSQL. The connection, on the other hand, may be done via a bridge. If, for instance you specify an ODBC connection then the ADO to ODBC bridge would be used. 

Remember that performance issues are typically caused not by the specific techonology that's used (though that's a factor), but by the way people fetch and process data. 

[kking124]

Thanks. Since the DB kit is ADO, then would OLE DB also be a bridge? It's been my understanding, from everything I've read on OLE DB vs ODBC, that OLE DB is MS's abstraction layer that basically provides uniform access to different kinds of drivers for different types of data (relational, mult-dimensional, etc), and when it does relational databases it just calls the ODBC drivers. If that's correct, then there *shouldn't* be any real difference which way I do it, though ODBC would technically be lower level and use fewer resources and OLE DB offers more record security?

[smercurio_fc]

---

kking124 wrote:

Thanks. Since the DB kit is ADO, then would OLE DB also be a bridge?

---

I'm not an expert on this, but I think ... not necessarily. I think it depends on whether you have a native ADO driver for whatever database you're connecting to. I think a Google search may be able to provide more information for you. 

To be honest I've never really given this much thought.