Only sa Connects using SQLOLEDB provider

Only sa Connects using SQLOLEDB provider

Post by Thomas Stephen Laxa » Thu, 30 Dec 1999 04:00:00



 The code below seems to work only when the login Id is "sa".  I am not able
to get a connection with a regular user.  The server in question has only
users authenticated through NT.  So, technically, "sa" shouldn't be able to
log in from a client, since we have no NT user called "sa".

The user I am trying to connect with has full permissions (dbo) in the pubs
database.   Those permissions are by way of an NT Group mapped to a db role.
By the way, the exact same situation occurs when going through Crystal
Reports using Server Type:

OLEDB - SQLOLEDB - Microsoft OLE DB Provider for SQL  Server

Only "sa" can get a connection.

Anyone have an idea what the solution is to this security/connectivity
issue?

The code:

(Extracted from Otey & Conte's SQL Server 7 Developer's Guide,
Osborne/McGraw Hill)

    Dim cn As New ADODB.Connection

    ' Use the global connection object - cn
    ' Connect using the OLE DB provider for SQL Server - SQLOLEDB
    cn.ConnectionString = "PROVIDER=SQLOLEDB" & _
        ";SERVER=" & txtServer & _
        ";UID=" & txtLoginId & _
        ";PWD=" & txtPassword & _
        ";DATABASE=pubs"
    cn.Open
    cn.Close

 
 
 

Only sa Connects using SQLOLEDB provider

Post by Jack » Fri, 31 Dec 1999 04:00:00


Hi,I think this question may to set the permission in SQL Server.
You can add one new Login in the SQL Server.And you can set this login
in the system role.
Jack.K

 
 
 

Only sa Connects using SQLOLEDB provider

Post by Silver Drago » Fri, 31 Dec 1999 04:00:00


You could try the following:

strConn = "Provider=SQLOLEDB.1;
                Integrated Security=SSPI;
                Persist Security Info=False;
                Initial Catalog=PUBS;
                Data Source=SERVER"

___________________________________



Quote:> The code below seems to work only when the login Id is "sa".  I am not
able
> to get a connection with a regular user.  The server in question has only
> users authenticated through NT.  So, technically, "sa" shouldn't be able
to
> log in from a client, since we have no NT user called "sa".

> The user I am trying to connect with has full permissions (dbo) in the
pubs
> database.   Those permissions are by way of an NT Group mapped to a db
role.
> By the way, the exact same situation occurs when going through Crystal
> Reports using Server Type:

> OLEDB - SQLOLEDB - Microsoft OLE DB Provider for SQL  Server

> Only "sa" can get a connection.

> Anyone have an idea what the solution is to this security/connectivity
> issue?

> The code:

> (Extracted from Otey & Conte's SQL Server 7 Developer's Guide,
> Osborne/McGraw Hill)

>     Dim cn As New ADODB.Connection

>     ' Use the global connection object - cn
>     ' Connect using the OLE DB provider for SQL Server - SQLOLEDB
>     cn.ConnectionString = "PROVIDER=SQLOLEDB" & _
>         ";SERVER=" & txtServer & _
>         ";UID=" & txtLoginId & _
>         ";PWD=" & txtPassword & _
>         ";DATABASE=pubs"
>     cn.Open
>     cn.Close

 
 
 

1. Can't connect using OleDB provider SQLOLEDB

I've written a program using OLEDB (vc++6 sp3) that retrieves data from
my SQL Server 7.0 DB. I use the OLEDB for ODBC provider (MSDASQL) , but
now I want to use the native OleDB provider for SQL server(SQLOLEDB).
However I can't connect using following code:

  CDBPropSet dbinit(DBPROPSET_DBINIT);

  dbinit.AddProperty(DBPROP_AUTH_PASSWORD, OLESTR("joe"));
  dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("joe"));
  dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
  dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("MyDataBase"));
  dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
  dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);

  hr = db.Open(_T("SQLOLEDB"), &dbinit);

What heve I forgotten?

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

2. xml file storage with 1M 3K records on NTFS (crossposted)

3. OpenQuery using OLE DB provider 'SQLOLEDB'

4. Why always got error 10004, connection is busy ?

5. Can't retrieve temp table rows using SQLOLEDB provider

6. Oracle OCP looking for contracts

7. ASP Script using SQLOLEDB provider Question....

8. How to Copy a Stored Proc ?

9. streaming result sets to disk using the SQLOLEDB provider

10. SQLOLEDB subset using OLE DB Provider template

11. can't connect to sql server using sa

12. SQLOLEDB provider in SQL 7