query a LDAP server from MS SQL

I try to connect to a LDAP server from SQL query analyzer with the following string

(SELECT * FROM OPENQUERY
(ADSI,'SELECT * FROM ''LDAP://DC=test-02,DC=test,CD=no'' '))

This resulted in a error meesage:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [Non-interface error: OLE DB provider ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without DBPROPVAL_OO_BLOB being supported].
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

What is the problem ? It is something wrong with my db-setup, the query or what ? I try to do the same thing in a VB-program and that worked fine.Did you setup the linked server?

exec sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

instead of select * try and name them to see if you can at least return something

SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://DC=test-02,DC=test,CD=no'' ')

also, if objects are in containers you can specify the container name by CN= or if in OU's - OU=

if you are only wanting users you can specify

WHERE objectCategory = ''Person'' AND objectClass = ''user''

HTHsql

Query

Hi,

I have a table called Strategic Programmes which has 2 columns "ProgNo" (unique ID) and "ProgName"

I need to connect to SQL Server from Access using connection and recordset objects to find the next highest "ProgNo"

What is the SQL query I need to execute to find the next highest ID number and how do I extract this from my recordset?

Thanks,

Mark

Track:

This query will work both for SQL Server 2000 and SQL Server 2005:

select max (progNo)
from ( select top 2
progNo
from [Strategic Programmes]
order by progNo
) a

Note that it will perform better if the table is either keyed or indexed on the progNo column.

|||

If you are looking for "second" highest, above solutions works perfect.

But if you are looking for "next" highest, following is my way:

select
nextHighestProgNo = max(ProgNo)
from
YourTable
where
ProgNo < CurrentProgNo

If you are currennt ProgNo is 15 and if you execute above query by replaceing CurrentProgNo with 15, you will get the next highest ProgNo. (will be 14 if there is one)

sql

QA green button grayed out

I'm running QA on my local PC and going through an ODBC
connection, I've been able to connect to SQL servers on
our network and execute TSQL statements.
Suddenly, last week, the green button is grayed out. No
matter what server. Everything else looks ok. All
features in EM seem to work. In QA, I can connect to the
correct DB and that seems ok. I can type in a command.
But, no green execute button!
I'm running SQL 2000 on my PC. I'm attaching to SQL 2000
and SQL 7.0 servers. The local OS is XP.
Any ideas?
Thanks,
DonHi
And does Ctrl+E work?
John
"Don" <ddachner@.hotmail.com> wrote in message
news:00ad01c3cfd7$463e4470$a501280a@.phx.gbl...
> I'm running QA on my local PC and going through an ODBC
> connection, I've been able to connect to SQL servers on
> our network and execute TSQL statements.
> Suddenly, last week, the green button is grayed out. No
> matter what server. Everything else looks ok. All
> features in EM seem to work. In QA, I can connect to the
> correct DB and that seems ok. I can type in a command.
> But, no green execute button!
> I'm running SQL 2000 on my PC. I'm attaching to SQL 2000
> and SQL 7.0 servers. The local OS is XP.
> Any ideas?
> Thanks,
> Don
>

QA goes, EM doesn't (previously posted on microsoft.public.sqlserver.server)

sory for repost but nobody on sqlserver.server helped me to solve this
:-(((((((((
---
AFAIK QA uses ODBC and EM uses OLE DB right?
anyway, I can connect using QA but not using EM.
tried with IP addredd too with no avail
what's wrong?
tia
Zarko
server: active-pasive cluster win2000as sp3, sql2000sp3a
client: win xp pro, sql2000sp3aWhat is the exact error that is generated?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

QA goes, EM doesn't

AFAIK QA uses ODBC and EM uses OLE DB right?
anyway, I can connect using QA but not using EM.
tried with IP addredd too with no avail
what's wrong?
tia
Zarko
server: active-pasive cluster win2000as sp3, sql2000sp3a
client: win xp pro, sql2000sp3aZarko
What error do you get?
"Zarko Jovanovic" <mind_less_s_pa_mm_a_g_a_i_n_s_t@.bigfoot.com> wrote in
message news:1074513866.763663@.internet.fina.hr...

quote:

---

> AFAIK QA uses ODBC and EM uses OLE DB right?
> anyway, I can connect using QA but not using EM.
> tried with IP addredd too with no avail
> what's wrong?
> tia
> Zarko
> server: active-pasive cluster win2000as sp3, sql2000sp3a
> client: win xp pro, sql2000sp3a
>

---

|||no error, EM just freezes, I tried to wait but after an hour I just have to
kill it. Tried to increase login timeout in EM to 100 sec but nothing again
Uri Dimant wrote:[QUOTE]
> Zarko
> What error do you get?
>
> "Zarko Jovanovic" <mind_less_s_pa_mm_a_g_a_i_n_s_t@.bigfoot.com> wrote
> in message news:1074513866.763663@.internet.fina.hr...|||Zarko
Have you tried to register the server in EM?
Have look at client network utility for the server.
"Zarko Jovanovic" <mind_less_s_pa_mm_a_g_a_i_n_s_t@.bigfoot.com> wrote in
message news:1074515788.992858@.internet.fina.hr...

quote:

---

> no error, EM just freezes, I tried to wait but after an hour I just have

---

to

quote:

---

> kill it. Tried to increase login timeout in EM to 100 sec but nothing

---

again

quote:

---

> Uri Dimant wrote:
>
>

---

|||Well, that's the problem, I cannot register it since it freezes while trying
to connect. On the other machine everything works fine, both machine ping
server by name and IP.
Uri Dimant wrote:[QUOTE]
> Zarko
> Have you tried to register the server in EM?
> Have look at client network utility for the server.
>
>
> "Zarko Jovanovic" <mind_less_s_pa_mm_a_g_a_i_n_s_t@.bigfoot.com> wrote
> in message news:1074515788.992858@.internet.fina.hr...

QA goes, EM doesn't

AFAIK QA uses ODBC and EM uses OLE DB right?
anyway, I can connect using QA but not using EM.
tried with IP addredd too with no avail
what's wrong?
tia
Zarko
server: active-pasive cluster win2000as sp3, sql2000sp3a
client: win xp pro, sql2000sp3aZarko
What error do you get?
"Zarko Jovanovic" <mind_less_s_pa_mm_a_g_a_i_n_s_t@.bigfoot.com> wrote in
message news:1074513866.763663@.internet.fina.hr...
> AFAIK QA uses ODBC and EM uses OLE DB right?
> anyway, I can connect using QA but not using EM.
> tried with IP addredd too with no avail
> what's wrong?
> tia
> Zarko
> server: active-pasive cluster win2000as sp3, sql2000sp3a
> client: win xp pro, sql2000sp3a
>|||no error, EM just freezes, I tried to wait but after an hour I just have to
kill it. Tried to increase login timeout in EM to 100 sec but nothing again
Uri Dimant wrote:
> Zarko
> What error do you get?
>
> "Zarko Jovanovic" <mind_less_s_pa_mm_a_g_a_i_n_s_t@.bigfoot.com> wrote
> in message news:1074513866.763663@.internet.fina.hr...
>> AFAIK QA uses ODBC and EM uses OLE DB right?
>> anyway, I can connect using QA but not using EM.
>> tried with IP addredd too with no avail
>> what's wrong?
>> tia
>> Zarko
>> server: active-pasive cluster win2000as sp3, sql2000sp3a
>> client: win xp pro, sql2000sp3a|||Zarko
Have you tried to register the server in EM?
Have look at client network utility for the server.
"Zarko Jovanovic" <mind_less_s_pa_mm_a_g_a_i_n_s_t@.bigfoot.com> wrote in
message news:1074515788.992858@.internet.fina.hr...
> no error, EM just freezes, I tried to wait but after an hour I just have
to
> kill it. Tried to increase login timeout in EM to 100 sec but nothing
again
> Uri Dimant wrote:
> > Zarko
> > What error do you get?
> >
> >
> > "Zarko Jovanovic" <mind_less_s_pa_mm_a_g_a_i_n_s_t@.bigfoot.com> wrote
> > in message news:1074513866.763663@.internet.fina.hr...
> >> AFAIK QA uses ODBC and EM uses OLE DB right?
> >>
> >> anyway, I can connect using QA but not using EM.
> >> tried with IP addredd too with no avail
> >> what's wrong?
> >> tia
> >>
> >> Zarko
> >>
> >> server: active-pasive cluster win2000as sp3, sql2000sp3a
> >> client: win xp pro, sql2000sp3a
>
>|||Well, that's the problem, I cannot register it since it freezes while trying
to connect. On the other machine everything works fine, both machine ping
server by name and IP.
Uri Dimant wrote:
> Zarko
> Have you tried to register the server in EM?
> Have look at client network utility for the server.
>
>
> "Zarko Jovanovic" <mind_less_s_pa_mm_a_g_a_i_n_s_t@.bigfoot.com> wrote
> in message news:1074515788.992858@.internet.fina.hr...
>> no error, EM just freezes, I tried to wait but after an hour I just
>> have to kill it. Tried to increase login timeout in EM to 100 sec
>> but nothing again
>> Uri Dimant wrote:
>> Zarko
>> What error do you get?
>>
>> "Zarko Jovanovic" <mind_less_s_pa_mm_a_g_a_i_n_s_t@.bigfoot.com>
>> wrote in message news:1074513866.763663@.internet.fina.hr...
>> AFAIK QA uses ODBC and EM uses OLE DB right?
>> anyway, I can connect using QA but not using EM.
>> tried with IP addredd too with no avail
>> what's wrong?
>> tia
>> Zarko
>> server: active-pasive cluster win2000as sp3, sql2000sp3a
>> client: win xp pro, sql2000sp3a

Q3: Trusted SQL Logins

I can not connect to my Local SQL Server 2005 in C# code,

it throws the error,

The user is not associated with a trusted SQL Server connection.

i tried to change the mode to Mixed Mode, but i don't see it, I see only Windows and SQL Server Auth mode,

i tried to change to SQL Server Mode, but none of user log will work, it says, this login is not in trusted connection,

so i would make any login associated with trusted account.
Thanks,

I have covered how to choose both Windows and SQL authentication in SQL Server in the thread below but if you are getting this error in a web application then the problem is not SQL Server but IIS. In IIS Deny anonymous users and in your Web.Config enable impersonation. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1894423&SiteID=1

|||

"Mixed mode" = "SQL Server and Windows authentication mode"

Maybe you are trying to login using a SQL Login when your authentication is Windows only? Perhaps you need to change your connections string:

Windows Auth

integrated secruity=true

SQL Auth

integrated security=false; uid=user;pwd=pword

HTH!

|||

Thanks for your reply, but i followed the steps, when I change it to SQLServer Authentication mode, it ask for log info, I put SA account, but it gives this error,

SA is not associated with Trusted Account.

Also I don't see the Security page as well as Mixed Mode option when i go to Server properties.
(On the Security page, under Server authentication, select the new server authentication mode, and then click OK)

Any idea,

Thanks,|||

Where are you trying to change the authentication? Open SSMS, right click your servername in Object Explorer, click properties. Down the left hand side you should have a list of pages including the security one where you need to make these changes.

Is this not what you see? Are you using Management Studio? What version of SQL Server is it?

|||You are not in the correct place because you have only two options Window or Both SQL Server and Windows is the only other option so right click on the name of your server in object explorer and go to properties then security. But you still need to enable the System Admin account because during installation if you choose Windows authentication SQL Server disables the SA account. Hope this helps.|||Thanks,

i found it and changed it but still i get an error when I try to connect to Database Engine using that sa account that i enabled it,

1) A connection was succesfully established with the server, but then an error occured during the login process. (provider: shared memory Provider, error: 0 - No process is on the other end of the pipe)(MSSql Server , Error:233)

2) from asp.net code, i get error like , sa is not in trusted connection list.

any idea,

Thanks,|||

I think you'll need to get yourself back to the SQL Configuration Manager and perhaps enable named pipes? I did a quick search on google for: "No process is on the other end of the pipe" and there were a fair few resources. Have a look there and let us know how you get on.

Good luck!

|||

No you need to enable the SA account before you try to use it and error 233 is covered in the link below and you need to reboot the box after you make all these changes. I have also seen error 233 related to certificates not installed in SQL Server. Hope this helps.

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

Q2: Trusted SQL Logins

I can not connect to my Local SQL Server 2005 in C# code,

it throws the error,

The user is not associated with a trusted SQL Server connection.

i tried to change the mode to Mixed Mode, but i don't see it, I see only Windows and SQL Server Auth mode,

i tried to change to SQL Server Mode, but none of user log will work, it says, this login is not in trusted connection,

so i would make any login associated with trusted account.
Thanks,

I have covered how to choose both Windows and SQL authentication in SQL Server in the thread below but if you are getting this error in a web application then the problem is not SQL Server but IIS. In IIS Deny anonymous users and in your Web.Config enable impersonation. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1894423&SiteID=1

|||

"Mixed mode" = "SQL Server and Windows authentication mode"

Maybe you are trying to login using a SQL Login when your authentication is Windows only? Perhaps you need to change your connections string:

Windows Auth

integrated secruity=true

SQL Auth

integrated security=false; uid=user;pwd=pword

HTH!

|||

Thanks for your reply, but i followed the steps, when I change it to SQLServer Authentication mode, it ask for log info, I put SA account, but it gives this error,

SA is not associated with Trusted Account.

Also I don't see the Security page as well as Mixed Mode option when i go to Server properties.
(On the Security page, under Server authentication, select the new server authentication mode, and then click OK)

Any idea,

Thanks,|||

Where are you trying to change the authentication? Open SSMS, right click your servername in Object Explorer, click properties. Down the left hand side you should have a list of pages including the security one where you need to make these changes.

Is this not what you see? Are you using Management Studio? What version of SQL Server is it?

|||You are not in the correct place because you have only two options Window or Both SQL Server and Windows is the only other option so right click on the name of your server in object explorer and go to properties then security. But you still need to enable the System Admin account because during installation if you choose Windows authentication SQL Server disables the SA account. Hope this helps.|||Thanks,

i found it and changed it but still i get an error when I try to connect to Database Engine using that sa account that i enabled it,

1) A connection was succesfully established with the server, but then an error occured during the login process. (provider: shared memory Provider, error: 0 - No process is on the other end of the pipe)(MSSql Server , Error:233)

2) from asp.net code, i get error like , sa is not in trusted connection list.

any idea,

Thanks,|||

I think you'll need to get yourself back to the SQL Configuration Manager and perhaps enable named pipes? I did a quick search on google for: "No process is on the other end of the pipe" and there were a fair few resources. Have a look there and let us know how you get on.

Good luck!

|||

No you need to enable the SA account before you try to use it and error 233 is covered in the link below and you need to reboot the box after you make all these changes. I have also seen error 233 related to certificates not installed in SQL Server. Hope this helps.

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx