Connecting to SQL from Access

I have users who currently query a SQL database using Access and an odbc
connection. The database is being moved to a less secure server, and we are
looking for the best way for users to connect to this database so they can
do their own queries. They like Access because they can select the tables
and don't have to remember t-sql code, and I wouldn't want to give them sql
query anaylizer anyway. What would be the best way to do this? Eventually
we will be creating programs for them to access their data, but that could
be a long time in coming, and there are always queries we cannot anticipate
and therefore cannot provide an application for.
Thanks. Any ideas are appreciated.Access works pretty well. We've used access project files (.adp) to
allow users to query databases. .adp files utilize OLE DB connections.
You can also appropriately permission the login that the Access .adp
uses to access the sql server database. Let me know if you need more
specific guidance, I'd be happy to help out.|||Access works pretty well. We've used access project files (.adp) to
allow users to query databases. .adp files utilize OLE DB connections.
You can also appropriately permission the login that the Access .adp
uses to access the sql server database. Let me know if you need more
specific guidance, I'd be happy to help out.

Connecting to SQL

I have a problem in connecting to an SQL database on a server from laptop
computers. The users can access it from a desktop with same windows login as
they use on their laptop but all I get when they try on the laptop is an
error message saying connection failure. All machines are running XP and we
cannot find any differences in the setup.
If anyone has any advise or can point me in the right direction I would be
greatful.
TIA
Rob
Hi Rob,
Do you use sqlserver authentication or windows authentication on the
sqlserver ?
Ton
"Rob Fenn" <robfenn.mp@.nospamntlworld.com> wrote in message
news:OGxRj9rtEHA.2664@.TK2MSFTNGP12.phx.gbl...
> I have a problem in connecting to an SQL database on a server from laptop
> computers. The users can access it from a desktop with same windows login
as
> they use on their laptop but all I get when they try on the laptop is an
> error message saying connection failure. All machines are running XP and
we
> cannot find any differences in the setup.
> If anyone has any advise or can point me in the right direction I would be
> greatful.
> TIA
> Rob
>
>
|||windows authentication. Although this has been partly resolved. The laptops
have been assigned fixed IP addresses which has allowed them to connect.
However I would ideally prefer a different workaround as fixed IP addresses
are not ideal.
Thanks
"Ton" <t.leer@.wanadoo.nl> wrote in message
news:4176ad6b$0$30709$18b6e80@.news.wanadoo.nl...
> Hi Rob,
> Do you use sqlserver authentication or windows authentication on the
> sqlserver ?
> Ton
> "Rob Fenn" <robfenn.mp@.nospamntlworld.com> wrote in message
> news:OGxRj9rtEHA.2664@.TK2MSFTNGP12.phx.gbl...
> as
> we
>
|||This is a DNS issue and is not really related to SQL Server.
Michael Otey
"Rob Fenn" <robfenn.mp@.nospamntlworld.com> wrote in message
news:O1Ljfq4tEHA.2316@.TK2MSFTNGP12.phx.gbl...
> windows authentication. Although this has been partly resolved. The
laptops
> have been assigned fixed IP addresses which has allowed them to connect.
> However I would ideally prefer a different workaround as fixed IP
addresses[vbcol=seagreen]
> are not ideal.
> Thanks
> "Ton" <t.leer@.wanadoo.nl> wrote in message
> news:4176ad6b$0$30709$18b6e80@.news.wanadoo.nl...
laptop[vbcol=seagreen]
login[vbcol=seagreen]
an[vbcol=seagreen]
and
>

Connecting to SQL

I have a problem in connecting to an SQL database on a server from laptop
computers. The users can access it from a desktop with same windows login as
they use on their laptop but all I get when they try on the laptop is an
error message saying connection failure. All machines are running XP and we
cannot find any differences in the setup.
If anyone has any advise or can point me in the right direction I would be
greatful.
TIA
RobHi Rob,
Do you use sqlserver authentication or windows authentication on the
sqlserver ?
Ton
"Rob Fenn" <robfenn.mp@.nospamntlworld.com> wrote in message
news:OGxRj9rtEHA.2664@.TK2MSFTNGP12.phx.gbl...
> I have a problem in connecting to an SQL database on a server from laptop
> computers. The users can access it from a desktop with same windows login
as
> they use on their laptop but all I get when they try on the laptop is an
> error message saying connection failure. All machines are running XP and
we
> cannot find any differences in the setup.
> If anyone has any advise or can point me in the right direction I would be
> greatful.
> TIA
> Rob
>
>|||windows authentication. Although this has been partly resolved. The laptops
have been assigned fixed IP addresses which has allowed them to connect.
However I would ideally prefer a different workaround as fixed IP addresses
are not ideal.
Thanks
"Ton" <t.leer@.wanadoo.nl> wrote in message
news:4176ad6b$0$30709$18b6e80@.news.wanadoo.nl...
> Hi Rob,
> Do you use sqlserver authentication or windows authentication on the
> sqlserver ?
> Ton
> "Rob Fenn" <robfenn.mp@.nospamntlworld.com> wrote in message
> news:OGxRj9rtEHA.2664@.TK2MSFTNGP12.phx.gbl...
> as
> we
>|||This is a DNS issue and is not really related to SQL Server.
Michael Otey
"Rob Fenn" <robfenn.mp@.nospamntlworld.com> wrote in message
news:O1Ljfq4tEHA.2316@.TK2MSFTNGP12.phx.gbl...
> windows authentication. Although this has been partly resolved. The
laptops
> have been assigned fixed IP addresses which has allowed them to connect.
> However I would ideally prefer a different workaround as fixed IP
addresses
> are not ideal.
> Thanks
> "Ton" <t.leer@.wanadoo.nl> wrote in message
> news:4176ad6b$0$30709$18b6e80@.news.wanadoo.nl...
laptop[vbcol=seagreen]
login[vbcol=seagreen]
an[vbcol=seagreen]
and[vbcol=seagreen]
>

connecting to remote server

Hi friends
we've C# app that connects a sql server 2005. we've new requests from client that when users go offsite they still want use our app and connect to sql server.
my question is how can we connect to sql server remotely ?
is it using TCP/IP protocol ? if so how do we do that ?
Thanks for ur help.

Use SQL Server Configuration Manager, select protocols for your instance. On the right side, enable TCP protocol. You need restart the sql server. If you have firewall, don't forget to put your tcp port into exception, (also UDP port 1434 for sql browser if you have a named instance.)

|||Thanks for that Hong.
i've one more question on this.
what should my connection string contain ? should it contain IP address for server name ?
Thanks again|||btw we use patterns and practices library for our database connections.|||Either IP address or server name should work if your DNS works correctly.
Thanks.|||

Thank you very much for ur replies Hong.

still need some clarification as am not fully aware how DNS works .

currently in our connections string we specify our sql server name as "mallu" and other info like database etc.,(btw we use enterprise library for data access)

are you saying that once we open port on our server and allow remote connects abv connection string just works ? i mean say if we our server is in new zealand and user goes to say australia and he still be able connect ?

Thanks for clarification .

much appreciated you can point me to some resource on these issues on net

|||

Hi prk,

DNS is the Domain Name System -- essentially the directory service that translates your hostnames into IP address. Your network probably has one and your network admin should be able to give you more info.

Regardless of the geography, if your server is configured to listen to a remote protocol and the firewall (if any) is configured properly, you should be able to connect from your client app. The BOL contains some great information on how to do so:

http://msdn2.microsoft.com/en-us/library/ms190608.aspx
http://msdn2.microsoft.com/en-us/library/ms187853.aspx

Also, check out this tutorial on connecting to the database:

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

Il-Sung.

|||Il-Sung
wonderful mate. thank you very much for those links.
am looking something like that.will go thru those articles.
Thanks again.

Connecting to Named Instances

We have a few servers with named instances, 2003 Server w/ SQL 2000 SP3a
running Named Pipes only.
Our users primarily use IIS 5, 5.1, and 6 to connect to these servers with
ColdFusion and ASP .NET applications. Right now the ASP .NET applications
aren't able to connect to the server, here is an example of the connection
string:
user id=myuser;data source="SQLserver\SQLinstance";initial
catalog=mydatabase;password=mypassword
This doesn't work, and we're not quite sure why. Does any one also have
some good resources on managing multiple instances of SQL server from a SQL
admin and SQL developer point of view?
Thanks,
Trevor
These articles will help you troubleshoot.
265808 INF: How to Connect to an SQL Server 2000 Named Instance with the
http://support.microsoft.com/?id=265808
286303 INF: Behavior of SQL Server 2000 Network Library During Dynamic Port
http://support.microsoft.com/?id=286303
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connecting to Named Instances

We have a few servers with named instances, 2003 Server w/ SQL 2000 SP3a
running Named Pipes only.
Our users primarily use IIS 5, 5.1, and 6 to connect to these servers with
ColdFusion and ASP .NET applications. Right now the ASP .NET applications
aren't able to connect to the server, here is an example of the connection
string:
user id=myuser;data source="SQLserver\SQLinstance";initial
catalog=mydatabase;password=mypassword
This doesn't work, and we're not quite sure why. Does any one also have
some good resources on managing multiple instances of SQL server from a SQL
admin and SQL developer point of view?
Thanks,
TrevorThese articles will help you troubleshoot.
265808 INF: How to Connect to an SQL Server 2000 Named Instance with the
http://support.microsoft.com/?id=265808
286303 INF: Behavior of SQL Server 2000 Network Library During Dynamic Port
http://support.microsoft.com/?id=286303
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Connecting to another database

In a SQL Server database, there are views based on the tables from another
database on the same server. Is it possible that when the users open these
views (through the Web pages) they are connected to the database2 under
another login (not the login which is used to connect to the current
database)?
Thanks.
Hi Vik,
What is the purpose of this? If your web application connects to database2,
the view is not going to be the present there and you need to use
database1..view to connect to the view.
And if you want the web app to connect to database 2 under another login,
that has to be defined in your application's connection string.
Thanks
Yogish
|||No, if you SELECT from a view then you use the same connection. Of course, the application
programmer might have written the application so that it opens another connection using another
login...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vik" <viktorum@.==hotmail.com==> wrote in message news:OKVKhUqEFHA.3984@.TK2MSFTNGP14.phx.gbl...
> In a SQL Server database, there are views based on the tables from another database on the same
> server. Is it possible that when the users open these views (through the Web pages) they are
> connected to the database2 under another login (not the login which is used to connect to the
> current database)?
> Thanks.
>
|||In the db1, there are:
Table1,
View1 based on the db2 (should be read only),
View2 based on the Table1 and View2. The Web page is based on the View2,
that is the page connects to both databases using one connction string.
The users of the Web page log in with their own userid, and these logins
exist in the db1 as we need to record (using the triggers) which user
entered/modified data.
The users of the db1 need read-only access to the db2 (through the View1)
and could use one common login to the db2 as the db2 does not need to know
who connected to it with read-only access.
So, I wanted to use one datareader login in the db2 instead of creating the
logins in the db2 for each user of the db1. I supposed there may be a way to
specify a connection to the db2 in the View1 itself.
Thank you.
Vik
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:68ABC6F8-FC87-44C6-BD04-B6811725938B@.microsoft.com...
> Hi Vik,
> What is the purpose of this? If your web application connects to
> database2,
> the view is not going to be the present there and you need to use
> database1..view to connect to the view.
> And if you want the web app to connect to database 2 under another login,
> that has to be defined in your application's connection string.
> --
> Thanks
> Yogish
>
|||You should read about ownership chains and also about "Cross DB Ownership Chaining". If you don't
want the users to exists in the db where the table resides, you need to turn on cross database
ownership chaining. But you also need to make certain that you have proper owner for the tables and
views (so you don't get a broken ownership chain).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Vik" <viktorum@.==hotmail.com==> wrote in message news:eAu2TTrEFHA.392@.TK2MSFTNGP14.phx.gbl...
> In the db1, there are:
> Table1,
> View1 based on the db2 (should be read only),
> View2 based on the Table1 and View2. The Web page is based on the View2, that is the page connects
> to both databases using one connction string.
> The users of the Web page log in with their own userid, and these logins exist in the db1 as we
> need to record (using the triggers) which user entered/modified data.
> The users of the db1 need read-only access to the db2 (through the View1) and could use one common
> login to the db2 as the db2 does not need to know who connected to it with read-only access.
> So, I wanted to use one datareader login in the db2 instead of creating the logins in the db2 for
> each user of the db1. I supposed there may be a way to specify a connection to the db2 in the
> View1 itself.
> Thank you.
> Vik
>
> "Yogish" <yogishkamathg@.icqmail.com> wrote in message
> news:68ABC6F8-FC87-44C6-BD04-B6811725938B@.microsoft.com...
>

Connecting to another database

In a SQL Server database, there are views based on the tables from another
database on the same server. Is it possible that when the users open these
views (through the Web pages) they are connected to the database2 under
another login (not the login which is used to connect to the current
database)?
Thanks.Hi Vik,
What is the purpose of this? If your web application connects to database2,
the view is not going to be the present there and you need to use
database1..view to connect to the view.
And if you want the web app to connect to database 2 under another login,
that has to be defined in your application's connection string.
Thanks
Yogish|||No, if you SELECT from a view then you use the same connection. Of course, t
he application
programmer might have written the application so that it opens another conne
ction using another
login...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vik" <viktorum@.==hotmail.com==> wrote in message news:OKVKhUqEFHA.3984@.TK2MSFTNGP14.phx.gbl
..
> In a SQL Server database, there are views based on the tables from another
database on the same
> server. Is it possible that when the users open these views (through the W
eb pages) they are
> connected to the database2 under another login (not the login which is us
ed to connect to the
> current database)?
> Thanks.
>|||In the db1, there are:
Table1,
View1 based on the DB2 (should be read only),
View2 based on the Table1 and View2. The Web page is based on the View2,
that is the page connects to both databases using one connction string.
The users of the Web page log in with their own userid, and these logins
exist in the db1 as we need to record (using the triggers) which user
entered/modified data.
The users of the db1 need read-only access to the DB2 (through the View1)
and could use one common login to the DB2 as the DB2 does not need to know
who connected to it with read-only access.
So, I wanted to use one datareader login in the DB2 instead of creating the
logins in the DB2 for each user of the db1. I supposed there may be a way to
specify a connection to the DB2 in the View1 itself.
Thank you.
Vik
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:68ABC6F8-FC87-44C6-BD04-B6811725938B@.microsoft.com...
> Hi Vik,
> What is the purpose of this? If your web application connects to
> database2,
> the view is not going to be the present there and you need to use
> database1..view to connect to the view.
> And if you want the web app to connect to database 2 under another login,
> that has to be defined in your application's connection string.
> --
> Thanks
> Yogish
>|||You should read about ownership chains and also about "Cross DB Ownership Ch
aining". If you don't
want the users to exists in the db where the table resides, you need to turn
on cross database
ownership chaining. But you also need to make certain that you have proper o
wner for the tables and
views (so you don't get a broken ownership chain).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Vik" <viktorum@.==hotmail.com==> wrote in message news:eAu2TTrEFHA.392@.TK2MSFTNGP14.phx.gbl.
.
> In the db1, there are:
> Table1,
> View1 based on the DB2 (should be read only),
> View2 based on the Table1 and View2. The Web page is based on the View2, t
hat is the page connects
> to both databases using one connction string.
> The users of the Web page log in with their own userid, and these logins e
xist in the db1 as we
> need to record (using the triggers) which user entered/modified data.
> The users of the db1 need read-only access to the DB2 (through the View1)
and could use one common
> login to the DB2 as the DB2 does not need to know who connected to it with
read-only access.
> So, I wanted to use one datareader login in the DB2 instead of creating th
e logins in the DB2 for
> each user of the db1. I supposed there may be a way to specify a connectio
n to the DB2 in the
> View1 itself.
> Thank you.
> Vik
>
> "Yogish" <yogishkamathg@.icqmail.com> wrote in message
> news:68ABC6F8-FC87-44C6-BD04-B6811725938B@.microsoft.com...
>

Connecting to another database

In a SQL Server database, there are views based on the tables from another
database on the same server. Is it possible that when the users open these
views (through the Web pages) they are connected to the database2 under
another login (not the login which is used to connect to the current
database)?
Thanks.No, if you SELECT from a view then you use the same connection. Of course, the application
programmer might have written the application so that it opens another connection using another
login...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vik" <viktorum@.==hotmail.com==> wrote in message news:OKVKhUqEFHA.3984@.TK2MSFTNGP14.phx.gbl...
> In a SQL Server database, there are views based on the tables from another database on the same
> server. Is it possible that when the users open these views (through the Web pages) they are
> connected to the database2 under another login (not the login which is used to connect to the
> current database)?
> Thanks.
>|||Hi Vik,
What is the purpose of this? If your web application connects to database2,
the view is not going to be the present there and you need to use
database1..view to connect to the view.
And if you want the web app to connect to database 2 under another login,
that has to be defined in your application's connection string.
--
Thanks
Yogish|||In the db1, there are:
Table1,
View1 based on the db2 (should be read only),
View2 based on the Table1 and View2. The Web page is based on the View2,
that is the page connects to both databases using one connction string.
The users of the Web page log in with their own userid, and these logins
exist in the db1 as we need to record (using the triggers) which user
entered/modified data.
The users of the db1 need read-only access to the db2 (through the View1)
and could use one common login to the db2 as the db2 does not need to know
who connected to it with read-only access.
So, I wanted to use one datareader login in the db2 instead of creating the
logins in the db2 for each user of the db1. I supposed there may be a way to
specify a connection to the db2 in the View1 itself.
Thank you.
Vik
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:68ABC6F8-FC87-44C6-BD04-B6811725938B@.microsoft.com...
> Hi Vik,
> What is the purpose of this? If your web application connects to
> database2,
> the view is not going to be the present there and you need to use
> database1..view to connect to the view.
> And if you want the web app to connect to database 2 under another login,
> that has to be defined in your application's connection string.
> --
> Thanks
> Yogish
>|||You should read about ownership chains and also about "Cross DB Ownership Chaining". If you don't
want the users to exists in the db where the table resides, you need to turn on cross database
ownership chaining. But you also need to make certain that you have proper owner for the tables and
views (so you don't get a broken ownership chain).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Vik" <viktorum@.==hotmail.com==> wrote in message news:eAu2TTrEFHA.392@.TK2MSFTNGP14.phx.gbl...
> In the db1, there are:
> Table1,
> View1 based on the db2 (should be read only),
> View2 based on the Table1 and View2. The Web page is based on the View2, that is the page connects
> to both databases using one connction string.
> The users of the Web page log in with their own userid, and these logins exist in the db1 as we
> need to record (using the triggers) which user entered/modified data.
> The users of the db1 need read-only access to the db2 (through the View1) and could use one common
> login to the db2 as the db2 does not need to know who connected to it with read-only access.
> So, I wanted to use one datareader login in the db2 instead of creating the logins in the db2 for
> each user of the db1. I supposed there may be a way to specify a connection to the db2 in the
> View1 itself.
> Thank you.
> Vik
>
> "Yogish" <yogishkamathg@.icqmail.com> wrote in message
> news:68ABC6F8-FC87-44C6-BD04-B6811725938B@.microsoft.com...
>> Hi Vik,
>> What is the purpose of this? If your web application connects to database2,
>> the view is not going to be the present there and you need to use
>> database1..view to connect to the view.
>> And if you want the web app to connect to database 2 under another login,
>> that has to be defined in your application's connection string.
>> --
>> Thanks
>> Yogish
>

Connecting to an external SQL 2000 Server from a Vista machine with SQL 2005 installed

hi there - I'm praying someone here can shed some light on this - after many google and web searches, I have found users with similar issues, but have yet to find a solution.

Problem:I have recently upgraded my desktop from Windows XP to Windows Vista Ultimate (clean install) and have migrated my local machine to SQL Server 2005. However I have a number of .Net projects that access databased on another server running SQL Server 2000 - prior to my upgrade to Vista, I was running SQL Server 2000 on my desktop as well. During the install, everything seemed to go well, and I also installed the SQL Server 2005 Service pack as instructed via the MSDN site. However, when attempting to test my .net projects on my desktop pc (ie visithttp://localhost), I encounter the error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

note that the server I am attempting to connectTO is the old SQL 2000 server - NOT 2005. I specify my connection info in the web.config - and it points to an external ip address on the web.

Can anyone shed some light on this? I've tried enabling remote access on my local SQL 2005 install, but I'm fairly sure thats for servers that want to connect to my desktop, rather than my desktop attempting to connect to external servers. Any help is greatly appreciated - I'm pulling my hair out over trying to figure this out! :)

To summarize my setup (if it helps)

Desktop PC:
Windows Vista (Ultimate)
SQL Server 2005
Visual Studio 2005
II 7.0
.Net 2.0 (ASP.Net Application)

External Server (co-located at a datacenter)
Windows Server 2003
SQL Server 2000
IIS 6.0
.Net 2.0 (ASP.Net Application)

Much appreciated,

Ted

http://forums.asp.net/thread/1476735.aspx|||

Hi Al - thanks for the reply!

I followed the instructions, but am still running into the error. To re-state, the server my code is trying to connect to is only running SQL Server 2000, not SQL Server 2005. Can you enlighten me as to why I'd be getting an error stating that it is trying to connect to a 2005 database?

Much appreciated!

|||Very confused, I believe there is a bug in the provider, I seen a post before with a msdn2 link. I'll try to find it and post it in this thread|||

Did anyone here got hold of this issue. Could someone shed some light ?

|||

I've just run into exactly the same issue as that described in the beginning of this thread. Is there a solution out there for this problem? I'm running Vista Ultimate and Visual Studio 2005, but only SQL Server Management Studio Express.

Connecting to a SQL Server 2005 Instance

I completed a default install that uses the server name: Server1
and completed an instance install on Server1 called Server1test with
the same users, etc.
I can connect remotely to Server1 using SQL Server 2005 Management
Studio, but when I try to connect to Server1\Server1test I get:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (Provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) (Microsoft SQL Server)
If I can connect to Server1 using the SA account why is it not allowing
the named instance (i.e., Server1\Server1test) to connect? While at
Server1 I can connect to Server1\Server1test using the SA account.
Thanks for any suggestions.
RBollinger
Let's see your ConnectString.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"robboll" <robboll@.hotmail.com> wrote in message
news:1169744212.563385.41730@.v33g2000cwv.googlegro ups.com...
>I completed a default install that uses the server name: Server1
> and completed an instance install on Server1 called Server1test with
> the same users, etc.
> I can connect remotely to Server1 using SQL Server 2005 Management
> Studio, but when I try to connect to Server1\Server1test I get:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (Provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified) (Microsoft SQL Server)
> If I can connect to Server1 using the SA account why is it not allowing
> the named instance (i.e., Server1\Server1test) to connect? While at
> Server1 I can connect to Server1\Server1test using the SA account.
> Thanks for any suggestions.
> RBollinger
>
|||When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:[vbcol=seagreen]
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----X---
> "robboll" <robb...@.hotmail.com> wrote in messagenews:1169744212.563385.41730@.v33g2000cwv.go oglegroups.com...
>
>
>
>
|||While these aren't ConnectString strings, they do tell me that you're using
SA to access the servers. This is not a good idea, but it's not why you
aren't getting connected. When you install SQL Server it does not support
SQL Server authentication unless it was specifically requested in the
interactive setup process. Each instance must be so configured. The SQL
Server Surface Area Configuration tools can help adjust these settings.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"robboll" <robboll@.hotmail.com> wrote in message
news:1169751617.893553.179310@.v33g2000cwv.googlegr oups.com...
When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:[vbcol=seagreen]
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----X---
> "robboll" <robb...@.hotmail.com> wrote in
> messagenews:1169744212.563385.41730@.v33g2000cwv.go oglegroups.com...
>
>
>
>

Connecting to a SQL Server 2005 Instance

I completed a default install that uses the server name: Server1
and completed an instance install on Server1 called Server1test with
the same users, etc.
I can connect remotely to Server1 using SQL Server 2005 Management
Studio, but when I try to connect to Server1\Server1test I get:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (Provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified) (Microsoft SQL Server)
If I can connect to Server1 using the SA account why is it not allowing
the named instance (i.e., Server1\Server1test) to connect? While at
Server1 I can connect to Server1\Server1test using the SA account.
Thanks for any suggestions.
RBollingerLet's see your ConnectString.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"robboll" <robboll@.hotmail.com> wrote in message
news:1169744212.563385.41730@.v33g2000cwv.googlegroups.com...
>I completed a default install that uses the server name: Server1
> and completed an instance install on Server1 called Server1test with
> the same users, etc.
> I can connect remotely to Server1 using SQL Server 2005 Management
> Studio, but when I try to connect to Server1\Server1test I get:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (Provider: SQL Network Interfaces, error: 26 - Error
> Locating Server/Instance Specified) (Microsoft SQL Server)
> If I can connect to Server1 using the SA account why is it not allowing
> the named instance (i.e., Server1\Server1test) to connect? While at
> Server1 I can connect to Server1\Server1test using the SA account.
> Thanks for any suggestions.
> RBollinger
>|||When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no right=
s=2E
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----=
--=AD---
> "robboll" <robb...@.hotmail.com> wrote in messagenews:1169744212.563385.41=
730@.v33g2000cwv.googlegroups.com...[vbcol=seagreen]
>
>
>
>
>
>
>=20|||While these aren't ConnectString strings, they do tell me that you're using
SA to access the servers. This is not a good idea, but it's not why you
aren't getting connected. When you install SQL Server it does not support
SQL Server authentication unless it was specifically requested in the
interactive setup process. Each instance must be so configured. The SQL
Server Surface Area Configuration tools can help adjust these settings.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"robboll" <robboll@.hotmail.com> wrote in message
news:1169751617.893553.179310@.v33g2000cwv.googlegroups.com...
When I connect to the default instance from a remote client using SQL
Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Works as expected
Whn I connect to the named instance from the same remote client using
SQL Server 2005 Management Studio it is:
Server type: Database Engine
Server name: Server1\server1test
Authentication: SQL Server Authentication
Login: sa
Password: mypassword
Result: Fails
On Jan 25, 12:12 pm, "William \(Bill\) Vaughn"
<billvaRemoveT...@.nwlink.com> wrote:[vbcol=seagreen]
> Let's see your ConnectString.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speakerwww.betav.com/blog/billvawww.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visitwww.hitchhikerguides.netto get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ----
-_---
> "robboll" <robb...@.hotmail.com> wrote in
> messagenews:1169744212.563385.41730@.v33g2000cwv.googlegroups.com...
>
>
>
>
>
>
>

Connecting to a remote server

You don't need to have MSDE on the webserver.
Either of those connection strings should work. Use
trusted connection if your users will be authenticating
against the database, use username and password (but not
sa!!) if you can't use trusted.
Sincerely,
Invotion Engineering Team
Advanced Microsoft Hosting Solutions
http://www.Invotion.com

>--Original Message--
>We are setting up a webserver (172.16.0.2) that lives in
the DMZ behind our
>firewall (172.16.0.1)
>I would like this server to connect to a db that lives
on an SQL Server
>(PDC: 192.168.16.2) on our internal lan.
>I have opened port 1433 on the firewall between
172.16.0.2 & 192.168.16.2
>On the webserver I have installed MSDE that the web app
currently connects
>to successfully whilst I determine how to connect it to
the internal server.
>In EM on the SQL Server I can now connect to the MSDE
server, have activated
>TCP/IP 1433 on MSDE, and all works well.
>I would like our Web application to used the internal
SQL Server as opposed
>to the local MSDE.
>In order to connect to the SQL Server at 192.168.16.2 is
MSDE required or
>can it be removed ?
>Do I simply need to specify the port in the querystring
in order to connect
>to the remote server ?
>What users/logins will need to be created in order for
the web app to
>connect to our internal server ?
>Should I use a trusted connection or specify
uid=sa;pwd=abc in the
>querystring ?
>The querystring I have developed so far to connect to
this server is:
> <appSettings>
> <add key="ConnectionString"
>value="server=192.168.16.2,1433;Trusted_Connection=true;d
atabase=store" />
> </appSettings>
>Or should I use:
> <appSettings>
> <add key="ConnectionString"
>value="server=192.168.16.2,1433;uid=sa;pwd=abc;database=s
tore" />
> </appSettings>
>Any assistance would be greatly appreciated.
>
>Thanks
>Murphy
>
>.
>Thanks for your assistance, I have got this to work now and successfully
removed MSDE from the WebServer and all is still working.
As my webserver and SQL server are in different subnets I was having
problems getting a trusted connection to work and could only open the
connection with sa, however I have read that this is not an advisable
practice...
My users will authenticate against the SQL server which will contain a table
of usernames and passwords.
When using the trusted connection does this mean that the account used by
IIS is the one that will connect to SQL server via the query string ? So if
IIS uses the IUSER_SERVER account then I guess this account must have access
to the SQL server db on the sql server machine.
Thanks
Murphy
"Invotion" <anonymous@.discussions.microsoft.com> wrote in message
news:1849201c41b13$9a998f20$a501280a@.phx
.gbl...
> You don't need to have MSDE on the webserver.
> Either of those connection strings should work. Use
> trusted connection if your users will be authenticating
> against the database, use username and password (but not
> sa!!) if you can't use trusted.
> Sincerely,
> Invotion Engineering Team
> Advanced Microsoft Hosting Solutions
> http://www.Invotion.com
>
> the DMZ behind our
> on an SQL Server
> 172.16.0.2 & 192.168.16.2
> currently connects
> the internal server.
> server, have activated
> SQL Server as opposed
> MSDE required or
> in order to connect
> the web app to
> uid=sa;pwd=abc in the
> this server is:
> atabase=store" />
> tore" />

Connecting for entire session, or just when performing an operation?

Here's a simple question for you:

Is it better to maintain a single open connection to the database for the
entire duration of the users session, or to connect, perform an operation
and then disconnect each time the user wants to do some work, within that
session?

If so, why?

Thanks,

RobinRobin Tucker (idontwanttobespammedanymore@.reallyidont.com) writes:
> Is it better to maintain a single open connection to the database for the
> entire duration of the users session, or to connect, perform an operation
> and then disconnect each time the user wants to do some work, within that
> session?

The normal procedure these days is to do both, That is, typically
your code goes:

cnn.Connect;
cmd = cnn.CreateCommand("SELECT ... ")
cmd.Execute
GetData
cmd.close
cnd.Disconnect

But behind the scenes the client library maintains a connection pool,
so when you say disconnect, the library lingers on the connection, so
if you recnnect within some period, the connection is reused. If you
do connect withine some time, typically 60 seconds, the connection is
closed for real.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It's my understanding that if you use a DataAdapter, that the
DataAdapter makes it appear to you that you're always connected, but
behind the scenes it releases and reqacquires the connection when
appropriate. Does this sound right to anyone?|||(shumaker@.cs.fsu.edu) writes:
> It's my understanding that if you use a DataAdapter, that the
> DataAdapter makes it appear to you that you're always connected, but
> behind the scenes it releases and reqacquires the connection when
> appropriate. Does this sound right to anyone?

Well, if you have connected explicitly, the DataAdapter will close the
connection. But if the connection is closed, the DataAdapter will open,
perform the operation and close.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Connected users

does anybody know how I get to obtain the number or connected users' list in
a base SQL Server for license control?
In Access I get through the function LDBUser_GetUsers of msldbusr.dll.
Thank you.Try sp_who.
All of the SPIDS below 20, I believe, are system and not counted as "users"
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:u3n9TfncDHA.3708@.tk2msftngp13.phx.gbl...
>
> does anybody know how I get to obtain the number or connected users' list
in
> a base SQL Server for license control?
> In Access I get through the function LDBUser_GetUsers of msldbusr.dll.
> Thank you.
>|||In SQL2K spids 1-50 are reserved for internal use
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Morgan" <mfears@.spamcop.net> wrote in message
news:OZ0TLzncDHA.2308@.TK2MSFTNGP12.phx.gbl...
> Try sp_who.
> All of the SPIDS below 20, I believe, are system and not counted as
"users"
> "Frank Dulk" <fdulk@.bol.com.br> wrote in message
> news:u3n9TfncDHA.3708@.tk2msftngp13.phx.gbl...
> >
> >
> >
> > does anybody know how I get to obtain the number or connected users'
list
> in
> > a base SQL Server for license control?
> >
> > In Access I get through the function LDBUser_GetUsers of msldbusr.dll.
> >
> > Thank you.
> >
> >
>|||Thanks for the clarification, Ray. Much appreciated.
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:ukjY87ncDHA.2112@.TK2MSFTNGP10.phx.gbl...
> In SQL2K spids 1-50 are reserved for internal use
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Morgan" <mfears@.spamcop.net> wrote in message
> news:OZ0TLzncDHA.2308@.TK2MSFTNGP12.phx.gbl...
> > Try sp_who.
> >
> > All of the SPIDS below 20, I believe, are system and not counted as
> "users"
> >
> > "Frank Dulk" <fdulk@.bol.com.br> wrote in message
> > news:u3n9TfncDHA.3708@.tk2msftngp13.phx.gbl...
> > >
> > >
> > >
> > > does anybody know how I get to obtain the number or connected users'
> list
> > in
> > > a base SQL Server for license control?
> > >
> > > In Access I get through the function LDBUser_GetUsers of msldbusr.dll.
> > >
> > > Thank you.
> > >
> > >
> >
> >
>

connect to SQL Server Express remotly

Hi,

I'm planning to develop asoftware working over SQL Server Express Edition, And I was wondering how would different users connect to sql server xe remotly from their machines? and is there a limitation to the number of users who could connect at same time? Is there a client app to installed on their machines, or its just the main db on the server?

Note: all users should be connected using LAN

thanks in advance

hi,

SQLExpress grant you the ability to connect from remote clients as long as you enable network protocols..

remote clients will connect to the desired server as specified in the connection string, where you can pass the "SERVER=ServerInstanceName;" setting (see some connection string samples at http://www.connectionstrings.com/)..

there's no built in limit in the number of concurrent users but 32767, the overall limit of SQL Server... obviously this only is a theoretical limit, as each connection consumes resources on the server, and SQLExpress only allow you to address up to 1gb of ram and only 1 physical processor... depending on the actual database design, data load, access method, concurrency on data and the like, you'll get out of resources with very fewer users... it has been usualy sayd you can serve up to 25 concurrent users with MSDE, the predecessor of SQLExpress (which has a physical limitation, a "Governor Workload" that kicks in when 8 specified batches are running simultaneusly, slowing down all workloads)... SQLExpress does not present the same "scaling-down" technology, but this kind of magic number, based on detached database access (default with Ado.Net, but not with the older ADO data access model), with short queries and transactions can probably be still correct.. perhaps something more then this is eventually possible as well... anyway, remember to spare on resources :D

remote clients only need to install database connectivity binaries... MDAC if you use ADO/Ado.Net using the the OLE DB provider (which now is usually installed and upgraded by Windows Update as well), the SQL Native Client in current develop mode, as everyone is passing to this provider, which offers lots of new interesting features...

regards

|||

In Addition to the above post...

Have a look at the following article that will show you how to enable the remote connections to the Database Engine.