My workstation is XP prof sp2 w/ office XP under a domain policy.
i act as "administrator" using Access XP to connect with SQL server.
i can view and add records to the SQL server.
at the same workstation, i act as "normal user with normal rights" using
Access XP to connect with SQL server.
i can view BUT cannot add records.
The odbc is the same one. i also use the same login account with SQL
server.
How come i cannot add records? Thanks a lot.
Tony
The issue is not with the client but with the SQL Server whereby your
user-mode connection doesn't have permission to update the server.
You'll need to update the SQL Server to add these permissions to the
databases you want to use. Do a query under 'GRANT' privileges topics on
Books On Line for more details for the queries you want.
| From: "Tony WONG" <x34@.hknet.com>
| Subject: Access using odbc to connect with SQL server
| Date: Mon, 1 Aug 2005 17:21:42 +0800
| Lines: 19
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#GsS1pnlFHA.572@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: 202.64.158.2
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2524
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| My workstation is XP prof sp2 w/ office XP under a domain policy.
|
| i act as "administrator" using Access XP to connect with SQL server.
|
| i can view and add records to the SQL server.
|
| at the same workstation, i act as "normal user with normal rights" using
| Access XP to connect with SQL server.
|
| i can view BUT cannot add records.
|
| The odbc is the same one. i also use the same login account with SQL
| server.
|
| How come i cannot add records? Thanks a lot.
|
| Tony
|
|
|
Showing posts with label workstation. Show all posts
Showing posts with label workstation. Show all posts
Sunday, March 11, 2012
Access using odbc to connect with SQL server
My workstation is XP prof sp2 w/ office XP under a domain policy.
i act as "administrator" using Access XP to connect with SQL server.
i can view and add records to the SQL server.
at the same workstation, i act as "normal user with normal rights" using
Access XP to connect with SQL server.
i can view BUT cannot add records.
The odbc is the same one. i also use the same login account with SQL
server.
How come i cannot add records? Thanks a lot.
TonyThe issue is not with the client but with the SQL Server whereby your
user-mode connection doesn't have permission to update the server.
You'll need to update the SQL Server to add these permissions to the
databases you want to use. Do a query under 'GRANT' privileges topics on
Books On Line for more details for the queries you want.
| From: "Tony WONG" <x34@.hknet.com>
| Subject: Access using odbc to connect with SQL server
| Date: Mon, 1 Aug 2005 17:21:42 +0800
| Lines: 19
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#GsS1pnlFHA.572@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: 202.64.158.2
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2524
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| My workstation is XP prof sp2 w/ office XP under a domain policy.
|
| i act as "administrator" using Access XP to connect with SQL server.
|
| i can view and add records to the SQL server.
|
| at the same workstation, i act as "normal user with normal rights" using
| Access XP to connect with SQL server.
|
| i can view BUT cannot add records.
|
| The odbc is the same one. i also use the same login account with SQL
| server.
|
| How come i cannot add records? Thanks a lot.
|
| Tony
|
|
|
i act as "administrator" using Access XP to connect with SQL server.
i can view and add records to the SQL server.
at the same workstation, i act as "normal user with normal rights" using
Access XP to connect with SQL server.
i can view BUT cannot add records.
The odbc is the same one. i also use the same login account with SQL
server.
How come i cannot add records? Thanks a lot.
TonyThe issue is not with the client but with the SQL Server whereby your
user-mode connection doesn't have permission to update the server.
You'll need to update the SQL Server to add these permissions to the
databases you want to use. Do a query under 'GRANT' privileges topics on
Books On Line for more details for the queries you want.
| From: "Tony WONG" <x34@.hknet.com>
| Subject: Access using odbc to connect with SQL server
| Date: Mon, 1 Aug 2005 17:21:42 +0800
| Lines: 19
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#GsS1pnlFHA.572@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: 202.64.158.2
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2524
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| My workstation is XP prof sp2 w/ office XP under a domain policy.
|
| i act as "administrator" using Access XP to connect with SQL server.
|
| i can view and add records to the SQL server.
|
| at the same workstation, i act as "normal user with normal rights" using
| Access XP to connect with SQL server.
|
| i can view BUT cannot add records.
|
| The odbc is the same one. i also use the same login account with SQL
| server.
|
| How come i cannot add records? Thanks a lot.
|
| Tony
|
|
|
Thursday, March 8, 2012
Access to SQL 2000 Server database over dialup much slower on XP P
We have an application that accesses a SQL database on a SQL 2000 Server.
When using a dialup (RAS) connection from a W2K workstation with the same
application there is no problem, database information is accessed with 3-5
mins.
Now the same SQL W2K Server with the same database being access by a XP Pro
workstation with the same application over the same dialup connection,
results in much slower performance.
Actually down right terrible, that same information is accessed in 40+mins!
What is it within XP Pro or the ODBC settings or ADO settings that would
impact this transfer so much.
Any ideas?
Thanks.
Tony
Hi
Have you checked the connection speeds to see if they are different?
If you download the MDAC component checker you can verify which versions of
MDAC are being used.
Also make sure that things like server side cursors are being used.
You may want to confirm using profiler that the queries are not different
and they take the same length of time to complete. This may also confirm if
you are setting the options differently.
John
"Tony Saucedo" wrote:
> We have an application that accesses a SQL database on a SQL 2000 Server.
> When using a dialup (RAS) connection from a W2K workstation with the same
> application there is no problem, database information is accessed with 3-5
> mins.
> Now the same SQL W2K Server with the same database being access by a XP Pro
> workstation with the same application over the same dialup connection,
> results in much slower performance.
> Actually down right terrible, that same information is accessed in 40+mins!
> What is it within XP Pro or the ODBC settings or ADO settings that would
> impact this transfer so much.
> Any ideas?
> Thanks.
> Tony
When using a dialup (RAS) connection from a W2K workstation with the same
application there is no problem, database information is accessed with 3-5
mins.
Now the same SQL W2K Server with the same database being access by a XP Pro
workstation with the same application over the same dialup connection,
results in much slower performance.
Actually down right terrible, that same information is accessed in 40+mins!
What is it within XP Pro or the ODBC settings or ADO settings that would
impact this transfer so much.
Any ideas?
Thanks.
Tony
Hi
Have you checked the connection speeds to see if they are different?
If you download the MDAC component checker you can verify which versions of
MDAC are being used.
Also make sure that things like server side cursors are being used.
You may want to confirm using profiler that the queries are not different
and they take the same length of time to complete. This may also confirm if
you are setting the options differently.
John
"Tony Saucedo" wrote:
> We have an application that accesses a SQL database on a SQL 2000 Server.
> When using a dialup (RAS) connection from a W2K workstation with the same
> application there is no problem, database information is accessed with 3-5
> mins.
> Now the same SQL W2K Server with the same database being access by a XP Pro
> workstation with the same application over the same dialup connection,
> results in much slower performance.
> Actually down right terrible, that same information is accessed in 40+mins!
> What is it within XP Pro or the ODBC settings or ADO settings that would
> impact this transfer so much.
> Any ideas?
> Thanks.
> Tony
Access to SQL 2000 Server database over dialup much slower on XP P
We have an application that accesses a SQL database on a SQL 2000 Server.
When using a dialup (RAS) connection from a W2K workstation with the same
application there is no problem, database information is accessed with 3-5
mins.
Now the same SQL W2K Server with the same database being access by a XP Pro
workstation with the same application over the same dialup connection,
results in much slower performance.
Actually down right terrible, that same information is accessed in 40+mins!
What is it within XP Pro or the ODBC settings or ADO settings that would
impact this transfer so much.
Any ideas?
Thanks.
TonyHi
Have you checked the connection speeds to see if they are different?
If you download the MDAC component checker you can verify which versions of
MDAC are being used.
Also make sure that things like server side cursors are being used.
You may want to confirm using profiler that the queries are not different
and they take the same length of time to complete. This may also confirm if
you are setting the options differently.
John
"Tony Saucedo" wrote:
> We have an application that accesses a SQL database on a SQL 2000 Server.
> When using a dialup (RAS) connection from a W2K workstation with the same
> application there is no problem, database information is accessed with 3-5
> mins.
> Now the same SQL W2K Server with the same database being access by a XP Pro
> workstation with the same application over the same dialup connection,
> results in much slower performance.
> Actually down right terrible, that same information is accessed in 40+mins!
> What is it within XP Pro or the ODBC settings or ADO settings that would
> impact this transfer so much.
> Any ideas?
> Thanks.
> Tony|||I did not notice the connection speed. I'll check it and the MDAC version. I
would have thought that XP Pro would have had a better version of MDAC, hmmm.
Ok.
If the connection and transfer works better with W2K, does that indicate
that Server side cursor is being used or not. Where can I check this setting?
Do you know if XP handles client-side cursor differently then W2K?
I'll have to try the profiler later since the server is at a remote site.
Thanks.
Tony
"John Bell" wrote:
> Hi
> Have you checked the connection speeds to see if they are different?
> If you download the MDAC component checker you can verify which versions of
> MDAC are being used.
> Also make sure that things like server side cursors are being used.
> You may want to confirm using profiler that the queries are not different
> and they take the same length of time to complete. This may also confirm if
> you are setting the options differently.
> John
> "Tony Saucedo" wrote:
> > We have an application that accesses a SQL database on a SQL 2000 Server.
> > When using a dialup (RAS) connection from a W2K workstation with the same
> > application there is no problem, database information is accessed with 3-5
> > mins.
> >
> > Now the same SQL W2K Server with the same database being access by a XP Pro
> > workstation with the same application over the same dialup connection,
> > results in much slower performance.
> > Actually down right terrible, that same information is accessed in 40+mins!
> >
> > What is it within XP Pro or the ODBC settings or ADO settings that would
> > impact this transfer so much.
> >
> > Any ideas?
> > Thanks.
> > Tony|||Hi Tony
The difference could be in what options are set, although I don't remember
any defaults being different unless on your Windows 2000 system you are
running a very old version. An application change would be the most likely to
change to location of the cursors. I assume you have not changed the database
between these two events?
Also have you checked ODBC tracing is not on?
Is the connection speed any different and are you using the correct drivers?
John
"Tony Saucedo" wrote:
> I did not notice the connection speed. I'll check it and the MDAC version. I
> would have thought that XP Pro would have had a better version of MDAC, hmmm.
> Ok.
> If the connection and transfer works better with W2K, does that indicate
> that Server side cursor is being used or not. Where can I check this setting?
> Do you know if XP handles client-side cursor differently then W2K?
> I'll have to try the profiler later since the server is at a remote site.
> Thanks.
> Tony
> "John Bell" wrote:
> > Hi
> >
> > Have you checked the connection speeds to see if they are different?
> >
> > If you download the MDAC component checker you can verify which versions of
> > MDAC are being used.
> >
> > Also make sure that things like server side cursors are being used.
> >
> > You may want to confirm using profiler that the queries are not different
> > and they take the same length of time to complete. This may also confirm if
> > you are setting the options differently.
> >
> > John
> > "Tony Saucedo" wrote:
> >
> > > We have an application that accesses a SQL database on a SQL 2000 Server.
> > > When using a dialup (RAS) connection from a W2K workstation with the same
> > > application there is no problem, database information is accessed with 3-5
> > > mins.
> > >
> > > Now the same SQL W2K Server with the same database being access by a XP Pro
> > > workstation with the same application over the same dialup connection,
> > > results in much slower performance.
> > > Actually down right terrible, that same information is accessed in 40+mins!
> > >
> > > What is it within XP Pro or the ODBC settings or ADO settings that would
> > > impact this transfer so much.
> > >
> > > Any ideas?
> > > Thanks.
> > > Tony
When using a dialup (RAS) connection from a W2K workstation with the same
application there is no problem, database information is accessed with 3-5
mins.
Now the same SQL W2K Server with the same database being access by a XP Pro
workstation with the same application over the same dialup connection,
results in much slower performance.
Actually down right terrible, that same information is accessed in 40+mins!
What is it within XP Pro or the ODBC settings or ADO settings that would
impact this transfer so much.
Any ideas?
Thanks.
TonyHi
Have you checked the connection speeds to see if they are different?
If you download the MDAC component checker you can verify which versions of
MDAC are being used.
Also make sure that things like server side cursors are being used.
You may want to confirm using profiler that the queries are not different
and they take the same length of time to complete. This may also confirm if
you are setting the options differently.
John
"Tony Saucedo" wrote:
> We have an application that accesses a SQL database on a SQL 2000 Server.
> When using a dialup (RAS) connection from a W2K workstation with the same
> application there is no problem, database information is accessed with 3-5
> mins.
> Now the same SQL W2K Server with the same database being access by a XP Pro
> workstation with the same application over the same dialup connection,
> results in much slower performance.
> Actually down right terrible, that same information is accessed in 40+mins!
> What is it within XP Pro or the ODBC settings or ADO settings that would
> impact this transfer so much.
> Any ideas?
> Thanks.
> Tony|||I did not notice the connection speed. I'll check it and the MDAC version. I
would have thought that XP Pro would have had a better version of MDAC, hmmm.
Ok.
If the connection and transfer works better with W2K, does that indicate
that Server side cursor is being used or not. Where can I check this setting?
Do you know if XP handles client-side cursor differently then W2K?
I'll have to try the profiler later since the server is at a remote site.
Thanks.
Tony
"John Bell" wrote:
> Hi
> Have you checked the connection speeds to see if they are different?
> If you download the MDAC component checker you can verify which versions of
> MDAC are being used.
> Also make sure that things like server side cursors are being used.
> You may want to confirm using profiler that the queries are not different
> and they take the same length of time to complete. This may also confirm if
> you are setting the options differently.
> John
> "Tony Saucedo" wrote:
> > We have an application that accesses a SQL database on a SQL 2000 Server.
> > When using a dialup (RAS) connection from a W2K workstation with the same
> > application there is no problem, database information is accessed with 3-5
> > mins.
> >
> > Now the same SQL W2K Server with the same database being access by a XP Pro
> > workstation with the same application over the same dialup connection,
> > results in much slower performance.
> > Actually down right terrible, that same information is accessed in 40+mins!
> >
> > What is it within XP Pro or the ODBC settings or ADO settings that would
> > impact this transfer so much.
> >
> > Any ideas?
> > Thanks.
> > Tony|||Hi Tony
The difference could be in what options are set, although I don't remember
any defaults being different unless on your Windows 2000 system you are
running a very old version. An application change would be the most likely to
change to location of the cursors. I assume you have not changed the database
between these two events?
Also have you checked ODBC tracing is not on?
Is the connection speed any different and are you using the correct drivers?
John
"Tony Saucedo" wrote:
> I did not notice the connection speed. I'll check it and the MDAC version. I
> would have thought that XP Pro would have had a better version of MDAC, hmmm.
> Ok.
> If the connection and transfer works better with W2K, does that indicate
> that Server side cursor is being used or not. Where can I check this setting?
> Do you know if XP handles client-side cursor differently then W2K?
> I'll have to try the profiler later since the server is at a remote site.
> Thanks.
> Tony
> "John Bell" wrote:
> > Hi
> >
> > Have you checked the connection speeds to see if they are different?
> >
> > If you download the MDAC component checker you can verify which versions of
> > MDAC are being used.
> >
> > Also make sure that things like server side cursors are being used.
> >
> > You may want to confirm using profiler that the queries are not different
> > and they take the same length of time to complete. This may also confirm if
> > you are setting the options differently.
> >
> > John
> > "Tony Saucedo" wrote:
> >
> > > We have an application that accesses a SQL database on a SQL 2000 Server.
> > > When using a dialup (RAS) connection from a W2K workstation with the same
> > > application there is no problem, database information is accessed with 3-5
> > > mins.
> > >
> > > Now the same SQL W2K Server with the same database being access by a XP Pro
> > > workstation with the same application over the same dialup connection,
> > > results in much slower performance.
> > > Actually down right terrible, that same information is accessed in 40+mins!
> > >
> > > What is it within XP Pro or the ODBC settings or ADO settings that would
> > > impact this transfer so much.
> > >
> > > Any ideas?
> > > Thanks.
> > > Tony
Access to SQL 2000 Server database over dialup much slower on XP P
We have an application that accesses a SQL database on a SQL 2000 Server.
When using a dialup (RAS) connection from a W2K workstation with the same
application there is no problem, database information is accessed with 3-5
mins.
Now the same SQL W2K Server with the same database being access by a XP Pro
workstation with the same application over the same dialup connection,
results in much slower performance.
Actually down right terrible, that same information is accessed in 40+mins!
What is it within XP Pro or the ODBC settings or ADO settings that would
impact this transfer so much.
Any ideas?
Thanks.
TonyHi
Have you checked the connection speeds to see if they are different?
If you download the MDAC component checker you can verify which versions of
MDAC are being used.
Also make sure that things like server side cursors are being used.
You may want to confirm using profiler that the queries are not different
and they take the same length of time to complete. This may also confirm if
you are setting the options differently.
John
"Tony Saucedo" wrote:
> We have an application that accesses a SQL database on a SQL 2000 Server.
> When using a dialup (RAS) connection from a W2K workstation with the same
> application there is no problem, database information is accessed with 3-5
> mins.
> Now the same SQL W2K Server with the same database being access by a XP Pr
o
> workstation with the same application over the same dialup connection,
> results in much slower performance.
> Actually down right terrible, that same information is accessed in 40+mins
!
> What is it within XP Pro or the ODBC settings or ADO settings that would
> impact this transfer so much.
> Any ideas?
> Thanks.
> Tony
When using a dialup (RAS) connection from a W2K workstation with the same
application there is no problem, database information is accessed with 3-5
mins.
Now the same SQL W2K Server with the same database being access by a XP Pro
workstation with the same application over the same dialup connection,
results in much slower performance.
Actually down right terrible, that same information is accessed in 40+mins!
What is it within XP Pro or the ODBC settings or ADO settings that would
impact this transfer so much.
Any ideas?
Thanks.
TonyHi
Have you checked the connection speeds to see if they are different?
If you download the MDAC component checker you can verify which versions of
MDAC are being used.
Also make sure that things like server side cursors are being used.
You may want to confirm using profiler that the queries are not different
and they take the same length of time to complete. This may also confirm if
you are setting the options differently.
John
"Tony Saucedo" wrote:
> We have an application that accesses a SQL database on a SQL 2000 Server.
> When using a dialup (RAS) connection from a W2K workstation with the same
> application there is no problem, database information is accessed with 3-5
> mins.
> Now the same SQL W2K Server with the same database being access by a XP Pr
o
> workstation with the same application over the same dialup connection,
> results in much slower performance.
> Actually down right terrible, that same information is accessed in 40+mins
!
> What is it within XP Pro or the ODBC settings or ADO settings that would
> impact this transfer so much.
> Any ideas?
> Thanks.
> Tony
Thursday, February 9, 2012
Access Denied on trying to connect
I've installed the latest MSDE version Re1A and MSDE Manager (from
Vale Software) and SQL works fine on the workstation on which msde is
installed. But when i try to create a connection with another
workstation, the connection fails.
I've installed msde on WinXP Pro SP2 with the following command
setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL
I could not install mdac 2.8 as it is a part of SP2 which i've
allready installed.
If i try to telnet ipadres 1433, i get:
Can not connect to host, connection failed.
Uninstall the firewall didn't help a bit.
When i setup the connection variables like
Servernaam (i use an ip-adres)
SQL username en password
I can't select a database
If i test the connection, i get an error MS Data Link Error:
[DBNETLIB][ConnectionOpen(Connect()).] The SQL server does not exist
or access denied.
I've also to set the Network Library to dbmssocn, but no luck with
that.
MSDE Manager also can't connect form the client workstation. MSDE
Manager works fine on the host.
What am i missing?
PLEASE Help me
Frank
It is possible SQL is NOT listening on TCP. Thus, connections from the SQL
Server machine are successful because on the Server a SHARED MEMORY
connection is being made. You can verify what netlibs SQL is listening on
by looking at the latest SQL Errorlog (defaut location is c:\program
files\micrososft sql server\mssql\LOG\ERRORLOG). You need to look for a
line in this error log which states:
SQL server listening on TCP, Shared Memory, Named Pipes.
If you only have Shared Memory on this list, then only local connections
are allowed. Starting with SQL Server Desktop Engine ( MSDE 2000) SP3a ,
only local connectivity is permitted, by default. If you want to permit
remote connections to SQL Server Desktop Engine, follow the steps in the
following article:
814130: How to help secure network connectivity for SQL Server 2000 local
databases
http://support.microsoft.com/?id=814130
Also, check these KB's:
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server -
ID: 841249
http://support.microsoft.com/?id=841249
How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL - ID:
841252
http://support.microsoft.com/?id=841252
You may not be able to connect to an instance of SQL Server that is - ID:
841394
http://support.microsoft.com/?id=841394
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Fany, Thanx for your reply,
It did help me get 1 step closer to have it working.
I can define a connection and test it succesfully. But when i try to do the
same with two different third party software the connection doesn't work
properly.
With MDSE manager; the program can't get a connection running, it can't get
the information of the sql host.
And practically the same happens with the second tool.
Why is it possible to test the connection succesfully but it doesn't allow
its content to be reviewed? I know I have all the usernames and passwords
correct.
I hope you can point me again in the right direction...
gtz Frank
"Fany Vargas [MSFT]" wrote:
> It is possible SQL is NOT listening on TCP. Thus, connections from the SQL
> Server machine are successful because on the Server a SHARED MEMORY
> connection is being made. You can verify what netlibs SQL is listening on
> by looking at the latest SQL Errorlog (defaut location is c:\program
> files\micrososft sql server\mssql\LOG\ERRORLOG). You need to look for a
> line in this error log which states:
> SQL server listening on TCP, Shared Memory, Named Pipes.
> If you only have Shared Memory on this list, then only local connections
> are allowed. Starting with SQL Server Desktop Engine ( MSDE 2000) SP3a ,
> only local connectivity is permitted, by default. If you want to permit
> remote connections to SQL Server Desktop Engine, follow the steps in the
> following article:
> 814130: How to help secure network connectivity for SQL Server 2000 local
> databases
> http://support.microsoft.com/?id=814130
> Also, check these KB's:
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server -
> ID: 841249
> http://support.microsoft.com/?id=841249
> How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL - ID:
> 841252
> http://support.microsoft.com/?id=841252
> You may not be able to connect to an instance of SQL Server that is - ID:
> 841394
> http://support.microsoft.com/?id=841394
>
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
|||Hi Frank,
Sorry, came late to this one and it may have been discussed before but:
1. Try connecting with the servername "(local)". This will use shared
memory.
2. Try seeing if tcp is enabled. Do this with svrnetcn.exe.
3. Try checking you don't have XP SP2 firewall enabled. If so and you want
to use tcp, you'll need to set up an exception for it.
4. Make sure you have at least MDAC 2.7 installed.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:207BFCBD-1ED7-46A9-B140-C06746927E26@.microsoft.com...[vbcol=seagreen]
> Fany, Thanx for your reply,
> It did help me get 1 step closer to have it working.
> I can define a connection and test it succesfully. But when i try to do
> the
> same with two different third party software the connection doesn't work
> properly.
> With MDSE manager; the program can't get a connection running, it can't
> get
> the information of the sql host.
> And practically the same happens with the second tool.
> Why is it possible to test the connection succesfully but it doesn't allow
> its content to be reviewed? I know I have all the usernames and passwords
> correct.
> I hope you can point me again in the right direction...
> gtz Frank
> "Fany Vargas [MSFT]" wrote:
|||Hi Greg,
Thanx for your reply,
1. using msde locally is no problem, this works fine
2. allready got that enabled
3. I got Symantec Internet Security 2003 installed and made a firewall rule
to allow full communication on ports 1433 and 1434
4. MDAC 2.8 can't be installed because MDAC is a part of SP2
I try making a connection to the server with: telnet servername 1433
But i get an error (translated): No connection can be made to the host on
port 1433. Connection failed.
To be sure is wasn't Symantec that was blocking traffic, i disabled the
firewall. But no luck there.
Is there something else i can check?
thanx,
Frank
"Greg Low [MVP]" wrote:
> Hi Frank,
> Sorry, came late to this one and it may have been discussed before but:
> 1. Try connecting with the servername "(local)". This will use shared
> memory.
> 2. Try seeing if tcp is enabled. Do this with svrnetcn.exe.
> 3. Try checking you don't have XP SP2 firewall enabled. If so and you want
> to use tcp, you'll need to set up an exception for it.
> 4. Make sure you have at least MDAC 2.7 installed.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
|||When you state "I can define a connection and test it succesfully. ", do
you mean from a remote machine? How did you "test and define" the
connection? For example did you create an ODBC DSN and that tested
successfully? If telnet failed then, etheir SQL is NOT listening on TCP,
there's a firewall blocking sql port 1433, or sql may not be listening on
port 1433 and may instead be listening on a different port.
1. Is SQL a default or a named instance?
2. Test the following commands from both a local and a remote server, if
sql is a named instance then in the OSQL commands below replace
<ipaddressofsqlmachine> with <ipaddressofsqlmachine\INSTANCENAME> and
replace <sqlserver_machine_name> with <sqlserver_machine_name\INSTANCE_NAME>
telnet ipaddressofsqlmachine 1433
telnet sqlserver_machine_name 1433
ping -a ipaddressofsqlmachine
ping sqlserver_machine_name
osql.exe -Stcp:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Stcp:ipaddressofsqlmachine,1433 -E -Q"select getdate()"
osql.exe -Sipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Slpc:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Snp:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Ssqlserver_machine_name -E -Q"select getdate()"
3. What does the latest sql errorlog (file named ERRORLOG with no
extension) state sql is listening on? Does it say tcp, shared memory and
named pipes? This errorlog will also state what port sql is listening on.
4. It seems the problem is SQL isn't listening on TCP. So please ensure
that SQL is listening on TCP (you can use the sql server network utility or
sql errorlog to do this)
5. Also, below is a good article on how to troubleshoot these kinds of
issues: How to troubleshoot connectivity issues in SQL Server 2000 - ID:
827422, http://support.microsoft.com/?id=827422
Best of luck!
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
Vale Software) and SQL works fine on the workstation on which msde is
installed. But when i try to create a connection with another
workstation, the connection fails.
I've installed msde on WinXP Pro SP2 with the following command
setup SAPWD="AStrongSAPwd" SECURITYMODE=SQL
I could not install mdac 2.8 as it is a part of SP2 which i've
allready installed.
If i try to telnet ipadres 1433, i get:
Can not connect to host, connection failed.
Uninstall the firewall didn't help a bit.
When i setup the connection variables like
Servernaam (i use an ip-adres)
SQL username en password
I can't select a database
If i test the connection, i get an error MS Data Link Error:
[DBNETLIB][ConnectionOpen(Connect()).] The SQL server does not exist
or access denied.
I've also to set the Network Library to dbmssocn, but no luck with
that.
MSDE Manager also can't connect form the client workstation. MSDE
Manager works fine on the host.
What am i missing?
PLEASE Help me
Frank
It is possible SQL is NOT listening on TCP. Thus, connections from the SQL
Server machine are successful because on the Server a SHARED MEMORY
connection is being made. You can verify what netlibs SQL is listening on
by looking at the latest SQL Errorlog (defaut location is c:\program
files\micrososft sql server\mssql\LOG\ERRORLOG). You need to look for a
line in this error log which states:
SQL server listening on TCP, Shared Memory, Named Pipes.
If you only have Shared Memory on this list, then only local connections
are allowed. Starting with SQL Server Desktop Engine ( MSDE 2000) SP3a ,
only local connectivity is permitted, by default. If you want to permit
remote connections to SQL Server Desktop Engine, follow the steps in the
following article:
814130: How to help secure network connectivity for SQL Server 2000 local
databases
http://support.microsoft.com/?id=814130
Also, check these KB's:
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server -
ID: 841249
http://support.microsoft.com/?id=841249
How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL - ID:
841252
http://support.microsoft.com/?id=841252
You may not be able to connect to an instance of SQL Server that is - ID:
841394
http://support.microsoft.com/?id=841394
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Fany, Thanx for your reply,
It did help me get 1 step closer to have it working.
I can define a connection and test it succesfully. But when i try to do the
same with two different third party software the connection doesn't work
properly.
With MDSE manager; the program can't get a connection running, it can't get
the information of the sql host.
And practically the same happens with the second tool.
Why is it possible to test the connection succesfully but it doesn't allow
its content to be reviewed? I know I have all the usernames and passwords
correct.
I hope you can point me again in the right direction...
gtz Frank
"Fany Vargas [MSFT]" wrote:
> It is possible SQL is NOT listening on TCP. Thus, connections from the SQL
> Server machine are successful because on the Server a SHARED MEMORY
> connection is being made. You can verify what netlibs SQL is listening on
> by looking at the latest SQL Errorlog (defaut location is c:\program
> files\micrososft sql server\mssql\LOG\ERRORLOG). You need to look for a
> line in this error log which states:
> SQL server listening on TCP, Shared Memory, Named Pipes.
> If you only have Shared Memory on this list, then only local connections
> are allowed. Starting with SQL Server Desktop Engine ( MSDE 2000) SP3a ,
> only local connectivity is permitted, by default. If you want to permit
> remote connections to SQL Server Desktop Engine, follow the steps in the
> following article:
> 814130: How to help secure network connectivity for SQL Server 2000 local
> databases
> http://support.microsoft.com/?id=814130
> Also, check these KB's:
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server -
> ID: 841249
> http://support.microsoft.com/?id=841249
> How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL - ID:
> 841252
> http://support.microsoft.com/?id=841252
> You may not be able to connect to an instance of SQL Server that is - ID:
> 841394
> http://support.microsoft.com/?id=841394
>
> Fany Vargas
> Microsoft Corporation
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
> Microsoft highly recommends that users with Internet access update their
> Microsoft software to better protect against viruses and security
> vulnerabilities. The easiest way to do this is to visit the following
> websites:
> http://www.microsoft.com/protect
> http://www.microsoft.com/security/guidance/default.mspx
>
|||Hi Frank,
Sorry, came late to this one and it may have been discussed before but:
1. Try connecting with the servername "(local)". This will use shared
memory.
2. Try seeing if tcp is enabled. Do this with svrnetcn.exe.
3. Try checking you don't have XP SP2 firewall enabled. If so and you want
to use tcp, you'll need to set up an exception for it.
4. Make sure you have at least MDAC 2.7 installed.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:207BFCBD-1ED7-46A9-B140-C06746927E26@.microsoft.com...[vbcol=seagreen]
> Fany, Thanx for your reply,
> It did help me get 1 step closer to have it working.
> I can define a connection and test it succesfully. But when i try to do
> the
> same with two different third party software the connection doesn't work
> properly.
> With MDSE manager; the program can't get a connection running, it can't
> get
> the information of the sql host.
> And practically the same happens with the second tool.
> Why is it possible to test the connection succesfully but it doesn't allow
> its content to be reviewed? I know I have all the usernames and passwords
> correct.
> I hope you can point me again in the right direction...
> gtz Frank
> "Fany Vargas [MSFT]" wrote:
|||Hi Greg,
Thanx for your reply,
1. using msde locally is no problem, this works fine
2. allready got that enabled
3. I got Symantec Internet Security 2003 installed and made a firewall rule
to allow full communication on ports 1433 and 1434
4. MDAC 2.8 can't be installed because MDAC is a part of SP2
I try making a connection to the server with: telnet servername 1433
But i get an error (translated): No connection can be made to the host on
port 1433. Connection failed.
To be sure is wasn't Symantec that was blocking traffic, i disabled the
firewall. But no luck there.
Is there something else i can check?
thanx,
Frank
"Greg Low [MVP]" wrote:
> Hi Frank,
> Sorry, came late to this one and it may have been discussed before but:
> 1. Try connecting with the servername "(local)". This will use shared
> memory.
> 2. Try seeing if tcp is enabled. Do this with svrnetcn.exe.
> 3. Try checking you don't have XP SP2 firewall enabled. If so and you want
> to use tcp, you'll need to set up an exception for it.
> 4. Make sure you have at least MDAC 2.7 installed.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
|||When you state "I can define a connection and test it succesfully. ", do
you mean from a remote machine? How did you "test and define" the
connection? For example did you create an ODBC DSN and that tested
successfully? If telnet failed then, etheir SQL is NOT listening on TCP,
there's a firewall blocking sql port 1433, or sql may not be listening on
port 1433 and may instead be listening on a different port.
1. Is SQL a default or a named instance?
2. Test the following commands from both a local and a remote server, if
sql is a named instance then in the OSQL commands below replace
<ipaddressofsqlmachine> with <ipaddressofsqlmachine\INSTANCENAME> and
replace <sqlserver_machine_name> with <sqlserver_machine_name\INSTANCE_NAME>
telnet ipaddressofsqlmachine 1433
telnet sqlserver_machine_name 1433
ping -a ipaddressofsqlmachine
ping sqlserver_machine_name
osql.exe -Stcp:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Stcp:ipaddressofsqlmachine,1433 -E -Q"select getdate()"
osql.exe -Sipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Slpc:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Snp:ipaddressofsqlmachine -E -Q"select getdate()"
osql.exe -Ssqlserver_machine_name -E -Q"select getdate()"
3. What does the latest sql errorlog (file named ERRORLOG with no
extension) state sql is listening on? Does it say tcp, shared memory and
named pipes? This errorlog will also state what port sql is listening on.
4. It seems the problem is SQL isn't listening on TCP. So please ensure
that SQL is listening on TCP (you can use the sql server network utility or
sql errorlog to do this)
5. Also, below is a good article on how to troubleshoot these kinds of
issues: How to troubleshoot connectivity issues in SQL Server 2000 - ID:
827422, http://support.microsoft.com/?id=827422
Best of luck!
Fany Vargas
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their
Microsoft software to better protect against viruses and security
vulnerabilities. The easiest way to do this is to visit the following
websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
Subscribe to:
Posts (Atom)