New to asp

I am in a learning mode here as all my previous work has been with php.
I am trying to make an OLE DB connection.to SQL Server Express on my local
machine using ASP and VBscript. I followed the instructions for making a
NewConnection.udl and double clicked that, filled in the spots and tested
the connection. Everything was OK. When I put it into my asp code, using
that string in the conn.Open statement, I got the error message that it
couldn't open the database that I had specified in the connection string.
(I copied and pasted that string from the .udl file where the test
connection succeeded). This was with Windows Authorization.
I also created a user with the name ASPpage and password asptest. I gave
that user all the privileges using Sql Server Manager Studio Express. When
I changed the string so that it had a user id and a password (SQL Server
authentication), I got the message that the login failed for ASPpage. Note,
also, that I couln not create a connection with that user in Sql Server
Manager Studio Express, but I could make a connection with Windows
authentication.
The failure for the one with the user id is that the user is not associated
with a trusted SQL server connection (even though he has all privileges).
Any suggestions on how to proceed.
Here is the code:
<%
dim q, num, i, acct, coName
set Conn = Server.CreateObject("ADODB.Connection")
q = "SELECT * FROM Company"
Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
Info=False;Initial Catalog=the_database_name;Data Source=LAPTOP\SQLEXPRESS"
set rs = Conn.Execute(q)
set of = Server.CreateObject("ADODB.Field")
num = rs.Count
for i=0 To num - 1
acct = of("accountNumber")
coName = of("companyName")
Response.Write(acct & " " & coName & "<br>")
next
conn.Close
%>
When I put in to user info, I removed the Integrated Security stuff.
Thanks for any help
Shelly (Sheldon)You look forgot changing Authentication Method from Windows Authentication
to Mixed Authentication (SQL Server and Windows Authentication) from Server
Properties\Security.
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13gfs30mhrjej53@.corp.supernews.com...
>I am in a learning mode here as all my previous work has been with php.
> I am trying to make an OLE DB connection.to SQL Server Express on my local
> machine using ASP and VBscript. I followed the instructions for making a
> NewConnection.udl and double clicked that, filled in the spots and tested
> the connection. Everything was OK. When I put it into my asp code, using
> that string in the conn.Open statement, I got the error message that it
> couldn't open the database that I had specified in the connection string.
> (I copied and pasted that string from the .udl file where the test
> connection succeeded). This was with Windows Authorization.
> I also created a user with the name ASPpage and password asptest. I gave
> that user all the privileges using Sql Server Manager Studio Express.
> When I changed the string so that it had a user id and a password (SQL
> Server authentication), I got the message that the login failed for
> ASPpage. Note, also, that I couln not create a connection with that user
> in Sql Server Manager Studio Express, but I could make a connection with
> Windows authentication.
> The failure for the one with the user id is that the user is not
> associated with a trusted SQL server connection (even though he has all
> privileges).
> Any suggestions on how to proceed.
> Here is the code:
> <%
> dim q, num, i, acct, coName
> set Conn = Server.CreateObject("ADODB.Connection")
> q = "SELECT * FROM Company"
> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
> Info=False;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXPRESS"
> set rs = Conn.Execute(q)
> set of = Server.CreateObject("ADODB.Field")
> num = rs.Count
> for i=0 To num - 1
> acct = of("accountNumber")
> coName = of("companyName")
> Response.Write(acct & " " & coName & "<br>")
> next
> conn.Close
> %>
> When I put in to user info, I removed the Integrated Security stuff.
> Thanks for any help
> Shelly (Sheldon)
>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
> You look forgot changing Authentication Method from Windows Authentication
> to Mixed Authentication (SQL Server and Windows Authentication) from
> Server Properties\Security.
I'm sorry but I don't understand what you are saying. Where do you find
"Server Properties"? When I tried a new connection and switched from
Windows Authentication to SQL Server, it didn't work.
> --
> Ekrem Önsoy
>
> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> news:13gfs30mhrjej53@.corp.supernews.com...
>>I am in a learning mode here as all my previous work has been with php.
>> I am trying to make an OLE DB connection.to SQL Server Express on my
>> local machine using ASP and VBscript. I followed the instructions for
>> making a NewConnection.udl and double clicked that, filled in the spots
>> and tested the connection. Everything was OK. When I put it into my asp
>> code, using that string in the conn.Open statement, I got the error
>> message that it couldn't open the database that I had specified in the
>> connection string. (I copied and pasted that string from the .udl file
>> where the test connection succeeded). This was with Windows
>> Authorization.
>> I also created a user with the name ASPpage and password asptest. I gave
>> that user all the privileges using Sql Server Manager Studio Express.
>> When I changed the string so that it had a user id and a password (SQL
>> Server authentication), I got the message that the login failed for
>> ASPpage. Note, also, that I couln not create a connection with that user
>> in Sql Server Manager Studio Express, but I could make a connection with
>> Windows authentication.
>> The failure for the one with the user id is that the user is not
>> associated with a trusted SQL server connection (even though he has all
>> privileges).
>> Any suggestions on how to proceed.
>> Here is the code:
>> <%
>> dim q, num, i, acct, coName
>> set Conn = Server.CreateObject("ADODB.Connection")
>> q = "SELECT * FROM Company"
>> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
>> Info=False;Initial Catalog=the_database_name;Data
>> Source=LAPTOP\SQLEXPRESS"
>> set rs = Conn.Execute(q)
>> set of = Server.CreateObject("ADODB.Field")
>> num = rs.Count
>> for i=0 To num - 1
>> acct = of("accountNumber")
>> coName = of("companyName")
>> Response.Write(acct & " " & coName & "<br>")
>> next
>> conn.Close
>> %>
>> When I put in to user info, I removed the Integrated Security stuff.
>> Thanks for any help
>> Shelly (Sheldon)
>>
>|||You get that setting you right click on the server name goto properties >
seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
But looking at your connection string you have an error ... Maybe a mistype.
But here are two possible connection strings for SQL 2005:
Trusted Connection:
Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
Security=SSPI;
SQL User Name Connection:
Server=myServerAddress;Database=myDataBase;User
ID=myUsername;Password=myPassword;Trusted_Connection=False;
Also note if you are trying to use integrated security in ASP, the IIS
username IUSR account must have access to SQL server not your account.
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Shelly" wrote:
> "Ekrem Ã?nsoy" <ekrem@.btegitim.com> wrote in message
> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
> > You look forgot changing Authentication Method from Windows Authentication
> > to Mixed Authentication (SQL Server and Windows Authentication) from
> > Server Properties\Security.
> I'm sorry but I don't understand what you are saying. Where do you find
> "Server Properties"? When I tried a new connection and switched from
> Windows Authentication to SQL Server, it didn't work.
>
> > --
> > Ekrem Ã?nsoy
> >
> >
> >
> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> > news:13gfs30mhrjej53@.corp.supernews.com...
> >>I am in a learning mode here as all my previous work has been with php.
> >>
> >> I am trying to make an OLE DB connection.to SQL Server Express on my
> >> local machine using ASP and VBscript. I followed the instructions for
> >> making a NewConnection.udl and double clicked that, filled in the spots
> >> and tested the connection. Everything was OK. When I put it into my asp
> >> code, using that string in the conn.Open statement, I got the error
> >> message that it couldn't open the database that I had specified in the
> >> connection string. (I copied and pasted that string from the .udl file
> >> where the test connection succeeded). This was with Windows
> >> Authorization.
> >>
> >> I also created a user with the name ASPpage and password asptest. I gave
> >> that user all the privileges using Sql Server Manager Studio Express.
> >> When I changed the string so that it had a user id and a password (SQL
> >> Server authentication), I got the message that the login failed for
> >> ASPpage. Note, also, that I couln not create a connection with that user
> >> in Sql Server Manager Studio Express, but I could make a connection with
> >> Windows authentication.
> >>
> >> The failure for the one with the user id is that the user is not
> >> associated with a trusted SQL server connection (even though he has all
> >> privileges).
> >>
> >> Any suggestions on how to proceed.
> >>
> >> Here is the code:
> >>
> >> <%
> >> dim q, num, i, acct, coName
> >> set Conn = Server.CreateObject("ADODB.Connection")
> >> q = "SELECT * FROM Company"
> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist Security
> >> Info=False;Initial Catalog=the_database_name;Data
> >> Source=LAPTOP\SQLEXPRESS"
> >> set rs = Conn.Execute(q)
> >> set of = Server.CreateObject("ADODB.Field")
> >> num = rs.Count
> >> for i=0 To num - 1
> >> acct = of("accountNumber")
> >> coName = of("companyName")
> >> Response.Write(acct & " " & coName & "<br>")
> >> next
> >> conn.Close
> >> %>
> >>
> >> When I put in to user info, I removed the Integrated Security stuff.
> >>
> >> Thanks for any help
> >>
> >> Shelly (Sheldon)
> >>
> >>
> >>
> >
>
>|||"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
> You get that setting you right click on the server name goto properties >
> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
Thanks. When I do that in SQL Server Management Studio Express and hit the
"Test" button, I get that the testing failed because the user is not
associated with a trusted SQL Serverver connection. (Error 18452)
> But looking at your connection string you have an error ... Maybe a
> mistype.
> But here are two possible connection strings for SQL 2005:
> Trusted Connection:
> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
> Security=SSPI;
What about "Provider"? Also, should I leave out "Persist Security Info"?
> SQL User Name Connection:
> Server=myServerAddress;Database=myDataBase;User
> ID=myUsername;Password=myPassword;Trusted_Connection=False;
What about "Provider"? Also, I can't really test this one further until I
somehow get those credentials working in SQL Server Management Studio
Express.
> Also note if you are trying to use integrated security in ASP, the IIS
> username IUSR account must have access to SQL server not your account.
Could you expand upon this please? Where do I find/set the IIS username?
Thanks.
Shelly
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Shelly" wrote:
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in the
>> >> connection string. (I copied and pasted that string from the .udl file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>|||"Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
> You get that setting you right click on the server name goto properties >
> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
> But looking at your connection string you have an error ... Maybe a
> mistype.
> But here are two possible connection strings for SQL 2005:
It was a typo.
> Trusted Connection:
> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
> Security=SSPI;
When I tried this string (without Provider, etc.) I got an error message of
"Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done". This was at the Conn.Open line.
Before I removed the Provider and Persist Security, it said that it failed
to open the_database_name as requested by the login.
Shelly
> SQL User Name Connection:
> Server=myServerAddress;Database=myDataBase;User
> ID=myUsername;Password=myPassword;Trusted_Connection=False;
> Also note if you are trying to use integrated security in ASP, the IIS
> username IUSR account must have access to SQL server not your account.
> Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Shelly" wrote:
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in the
>> >> connection string. (I copied and pasted that string from the .udl file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>|||Shelly,
Please be sure that you have a LOGIN named ASPpage (or whatever) in main
Security node.
After that, be sure of that you have an appropriate USER which is associated
with that LOGIN and has necessary rights to perform its jobs in that
database.
Fire up SSMSE and go to Server Properties and go to Security and change the
Server authentication as "SQL Server and Windows Authentication mode" and
restart your SQL Server service and then try again.
Checklist:
In SQL Server Express, remote connections are disabled by default. So go to
Surface Area Configurations and be sure your instance is enabled for TCP\IP
or whatever protocol you use for connection.
Go to SQL Server Configuration Manager and be sure your Network
Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
must be entered etc.)
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13ghjl7nvigltfb@.corp.supernews.com...
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
> It was a typo.
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
> When I tried this string (without Provider, etc.) I got an error message
> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done". This was at the Conn.Open
> line. Before I removed the Provider and Persist Security, it said that it
> failed to open the_database_name as requested by the login.
> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into my
>> >> asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:B7333908-981B-436F-8CB3-4676EC53B93F@.microsoft.com...
> Shelly,
> Please be sure that you have a LOGIN named ASPpage (or whatever) in main
> Security node.
I have one under the Security tab which is under the LAPTOP|SQLEXPRESS in
the object explorer in SSMSE.
> After that, be sure of that you have an appropriate USER which is
> associated with that LOGIN and has necessary rights to perform its jobs in
> that database.
I have a user under Databases\the_database_name\Security\Users in SSMSE. It
has the same rights as the Administrator, including Connect SQL.
> Fire up SSMSE and go to Server Properties and go to Security and change
> the Server authentication as "SQL Server and Windows Authentication mode"
> and restart your SQL Server service and then try again.
Already there at Windows Authentication Mode -- but I restarted anyway.
> Checklist:
> In SQL Server Express, remote connections are disabled by default. So go
> to Surface Area Configurations and be sure your instance is enabled for
> TCP\IP or whatever protocol you use for connection.
I don't know what you mean with "Surface Area Configurations" or where to
find it. I went to the configuration manager and saw to it that shared
memory, named pipes and tcpip were all enabled. I restarted the sql server.
> Go to SQL Server Configuration Manager and be sure your Network
> Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
> must be entered etc.)
Done. (Note that everything is taking place on my local laptop, called
LAPTOP, no external connections)
I still get the "Multiple step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work was done".
I don't know where or how to check these values.
My connection line is:
Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
Source=LAPTOP\SQLEXRESS"
Shelly
> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
> news:13ghjl7nvigltfb@.corp.supernews.com...
>> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
>> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties
>> >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
>> It was a typo.
>>
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
>> When I tried this string (without Provider, etc.) I got an error message
>> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
>> status value, if available. No work was done". This was at the
>> Conn.Open line. Before I removed the Provider and Persist Security, it
>> said that it failed to open the_database_name as requested by the login.
>> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you
>> find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into
>> >> my asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest. I
>> >> gave
>> >> that user all the privileges using Sql Server Manager Studio
>> >> Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with that
>> >> user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>>
>|||> Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXRESS"
Try "Server" instead of "Data Source" and "Database" instead of "Initial
Catalog".
--
Ekrem Önsoy
"Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
news:13gmnt5drgdse27@.corp.supernews.com...
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:B7333908-981B-436F-8CB3-4676EC53B93F@.microsoft.com...
>> Shelly,
>> Please be sure that you have a LOGIN named ASPpage (or whatever) in main
>> Security node.
> I have one under the Security tab which is under the LAPTOP|SQLEXPRESS in
> the object explorer in SSMSE.
>> After that, be sure of that you have an appropriate USER which is
>> associated with that LOGIN and has necessary rights to perform its jobs
>> in that database.
> I have a user under Databases\the_database_name\Security\Users in SSMSE.
> It has the same rights as the Administrator, including Connect SQL.
>> Fire up SSMSE and go to Server Properties and go to Security and change
>> the Server authentication as "SQL Server and Windows Authentication mode"
>> and restart your SQL Server service and then try again.
> Already there at Windows Authentication Mode -- but I restarted anyway.
>> Checklist:
>> In SQL Server Express, remote connections are disabled by default. So go
>> to Surface Area Configurations and be sure your instance is enabled for
>> TCP\IP or whatever protocol you use for connection.
> I don't know what you mean with "Surface Area Configurations" or where to
> find it. I went to the configuration manager and saw to it that shared
> memory, named pipes and tcpip were all enabled. I restarted the sql
> server.
>> Go to SQL Server Configuration Manager and be sure your Network
>> Configuration is OK. (TCP\IP or whatever is must be enabled, IP addresses
>> must be entered etc.)
> Done. (Note that everything is taking place on my local laptop, called
> LAPTOP, no external connections)
> I still get the "Multiple step OLE DB operation generated errors. Check
> each OLE DB status value, if available. No work was done".
> I don't know where or how to check these values.
> My connection line is:
> Conn.Open "Integrated Security=SSPI;Initial Catalog=the_database_name;Data
> Source=LAPTOP\SQLEXRESS"
> Shelly
>> "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> news:13ghjl7nvigltfb@.corp.supernews.com...
>> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
>> news:DAC9B2CE-E61A-4AFE-854F-224A4E7566AC@.microsoft.com...
>> You get that setting you right click on the server name goto properties
>> >
>> seucrity > seclect SQL Server and Windows Authentication as Ekrem said.
>> But looking at your connection string you have an error ... Maybe a
>> mistype.
>> But here are two possible connection strings for SQL 2005:
>> It was a typo.
>>
>> Trusted Connection:
>> Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated
>> Security=SSPI;
>> When I tried this string (without Provider, etc.) I got an error message
>> of "Multiple-step OLE DB operation generated errors. Check each OLE DB
>> status value, if available. No work was done". This was at the
>> Conn.Open line. Before I removed the Provider and Persist Security, it
>> said that it failed to open the_database_name as requested by the login.
>> Shelly
>> SQL User Name Connection:
>> Server=myServerAddress;Database=myDataBase;User
>> ID=myUsername;Password=myPassword;Trusted_Connection=False;
>> Also note if you are trying to use integrated security in ASP, the IIS
>> username IUSR account must have access to SQL server not your account.
>> Thanks!
>> --
>> Mohit K. Gupta
>> B.Sc. CS, Minor Japanese
>> MCTS: SQL Server 2005
>>
>> "Shelly" wrote:
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:B0697784-4125-4B7E-833F-AA905D02AB02@.microsoft.com...
>> > You look forgot changing Authentication Method from Windows
>> > Authentication
>> > to Mixed Authentication (SQL Server and Windows Authentication) from
>> > Server Properties\Security.
>> I'm sorry but I don't understand what you are saying. Where do you
>> find
>> "Server Properties"? When I tried a new connection and switched from
>> Windows Authentication to SQL Server, it didn't work.
>>
>> > --
>> > Ekrem Önsoy
>> >
>> >
>> >
>> > "Shelly" <sheldonlg.news@.asap-consult.com> wrote in message
>> > news:13gfs30mhrjej53@.corp.supernews.com...
>> >>I am in a learning mode here as all my previous work has been with
>> >>php.
>> >>
>> >> I am trying to make an OLE DB connection.to SQL Server Express on
>> >> my
>> >> local machine using ASP and VBscript. I followed the instructions
>> >> for
>> >> making a NewConnection.udl and double clicked that, filled in the
>> >> spots
>> >> and tested the connection. Everything was OK. When I put it into
>> >> my asp
>> >> code, using that string in the conn.Open statement, I got the error
>> >> message that it couldn't open the database that I had specified in
>> >> the
>> >> connection string. (I copied and pasted that string from the .udl
>> >> file
>> >> where the test connection succeeded). This was with Windows
>> >> Authorization.
>> >>
>> >> I also created a user with the name ASPpage and password asptest.
>> >> I gave
>> >> that user all the privileges using Sql Server Manager Studio
>> >> Express.
>> >> When I changed the string so that it had a user id and a password
>> >> (SQL
>> >> Server authentication), I got the message that the login failed for
>> >> ASPpage. Note, also, that I couln not create a connection with
>> >> that user
>> >> in Sql Server Manager Studio Express, but I could make a connection
>> >> with
>> >> Windows authentication.
>> >>
>> >> The failure for the one with the user id is that the user is not
>> >> associated with a trusted SQL server connection (even though he has
>> >> all
>> >> privileges).
>> >>
>> >> Any suggestions on how to proceed.
>> >>
>> >> Here is the code:
>> >>
>> >> <%
>> >> dim q, num, i, acct, coName
>> >> set Conn = Server.CreateObject("ADODB.Connection")
>> >> q = "SELECT * FROM Company"
>> >> Conn.Open "Provider=SQLOLEDB.1;Integrated Security-SSPI;Persist
>> >> Security
>> >> Info=False;Initial Catalog=the_database_name;Data
>> >> Source=LAPTOP\SQLEXPRESS"
>> >> set rs = Conn.Execute(q)
>> >> set of = Server.CreateObject("ADODB.Field")
>> >> num = rs.Count
>> >> for i=0 To num - 1
>> >> acct = of("accountNumber")
>> >> coName = of("companyName")
>> >> Response.Write(acct & " " & coName & "<br>")
>> >> next
>> >> conn.Close
>> >> %>
>> >>
>> >> When I put in to user info, I removed the Integrated Security
>> >> stuff.
>> >>
>> >> Thanks for any help
>> >>
>> >> Shelly (Sheldon)
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>|||"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:58AF86FF-ED77-47B7-97F5-3D1D3B7B7CCE@.microsoft.com...
>> Conn.Open "Integrated Security=SSPI;Initial
>> Catalog=the_database_name;Data Source=LAPTOP\SQLEXRESS"
> Try "Server" instead of "Data Source" and "Database" instead of "Initial
> Catalog".
> --
> Ekrem Önsoy
Same result.