OleDbConnection Good SQLConnection Bad What Th?

[Don Albertson]

Running SQLServer 2000 Developer Edition on Win2K Professional PC
The quickstart tutorial materials are trying to use SqlConnection
objects and fail.

When I modify them to use OleDbConnection objects there is no problem.

Here is the simplest code I can come up with that demonstrates the
situation:
========================Example that works=============================
Dim m_ConnectStr As String = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=True;Initial Catalog=pubs;Data
Source=MY_PC;Use Procedure for Prepare=1;Auto Translate=True;Packet
Size=4096;Workstation ID=MY_PC;Use Encryption for Data=False;Tag with
column collation when possible=False"

Dim MyConnection As System.Data.OleDb.OleDbConnection
MyConnection = New System.Data.OleDb.OleDbConnection(m_ConnectStr)
MyConnection.Open()
'Do stuff with MyConnection
MyConnection.Close()

========================Example that fails=============================
Dim m_ConnectStr As String =
"server=(local)\NetSDK;database=pubs;Trusted_Connection=yes"

Dim MyConnection As SqlConnection
MyConnection = New SqlConnection(m_ConnectStr)
MyConnection.Open()

Error message is:
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.


Figuring that the \NetSDK might not be quite right, I modified the
connection string slightly and got this
========================Different Example that fails===================
Dim m_ConnectStr As String =
"server=MY_PC;database=pubs;Trusted_Connection=yes"

Dim MyConnection As SqlConnection
MyConnection = New SqlConnection(m_ConnectStr)
MyConnection.Open()

Error Message is:
System.Security.SecurityException: Requested registry access is not allowed.

Anyone got a clue? I'm running short.

 

[S Gopikrishna]

Hi There

Can you try using the following Connection strings

1. Non Trusted Connection

"Data Source=ds;Initial Catalog=pubs;User
Id=sa;Password=asdasd;"
- or -
"Server=servname;Database=pubs;User
ID=sa;Password=asdasd;Trusted_Connection=False"


2. Trusted Connection:
"Data Source=myserv;Initial Catalog=pubs;Integrated
Security=SSPI;"
- or -
"Server=myserv;Database=pubs;Trusted_Connection=True;"

Thanks,
Gopi

-----Original Message-----
Running SQLServer 2000 Developer Edition on Win2K Professional PC
The quickstart tutorial materials are trying to use SqlConnection
objects and fail.

When I modify them to use OleDbConnection objects there is no problem.

Here is the simplest code I can come up with that demonstrates the
situation:
========================Example that works=============================
Dim m_ConnectStr As String

= "Provider=SQLOLEDB.1;Integrated

 

[Don Albertson]

Hi There

Can you try using the following Connection strings

1. Non Trusted Connection

"Data Source=ds;Initial Catalog=pubs;User
Id=sa;Password=asdasd;"
- or -
"Server=servname;Database=pubs;User
ID=sa;Password=asdasd;Trusted_Connection=False"


2. Trusted Connection:
"Data Source=myserv;Initial Catalog=pubs;Integrated
Security=SSPI;"
- or -
"Server=myserv;Database=pubs;Trusted_Connection=True;"

All 3 produce:
System.Security.SecurityException: Requested registry access is not allowed.

I'm pretty sure it's a case of user priveleges but not within SQLServer.
The users can connect to it via the OleDbConnection object just fine.
It appears that with the SqlConnection it never even gets to try to
log in to SQLServer due to some .NET security setting. Of course, it
would probably help if the system security were the same as it was when
..NET was installed but that was about a dozen patches ago.

 

[DGA]

All 3 produce:
System.Security.SecurityException: Requested registry access is not
allowed.

I'm pretty sure it's a case of user priveleges but not within SQLServer.
The users can connect to it via the OleDbConnection object just fine.
It appears that with the SqlConnection it never even gets to try to log
in to SQLServer due to some .NET security setting. Of course, it would
probably help if the system security were the same as it was when .NET
was installed but that was about a dozen patches ago.

But now they almost all work, except the ones that try to use
(local)\NetSDK. And all I had to do was to turn off the bloody thing,
go to work and muck about with Oracle all day and I come home and it has
suddenly "got better".