Connecting to remote sql server 2005

Dear All,

I am quite new to sql server 2005. Now I am going to develop a new asp.net application connecting to sql server 2005 remotely. So what must I have install in order to connect to a remote sql server and also to enable me build the tables too? What are the softwares that I need to install before I can proceed on this ? If possible I wnat to avoiding installing the whole server into my local machine as I dont need it. Thanks.

You might find these to be good resources to get you started out.

http://asp.net/learn/dataaccess/default.aspx?tabid=63

http://asp.net/learn/videos/default.aspx?tabid=63

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

|||

Dear Arnie,

Thanks for the link. Thus that means minimally I must install the sql express is it? Thanks.

|||

No, you do not 'have' to install SQL Express.

However, you might benefit from having a local copy to learn with and explore.

Minimally, you will need a client tool or environment that will connect to the SQL Server.

That client environment 'could be' Visual Studio.

It could also be the SQL Server Management Studio Express client tool.

|||

Dear Arnie,

I dont mind installing the express version. But I just want to know is it the same like the orginial sql server 2005 ? Thanks.

|||

Every thing that you can do in SQL Express, you can do in the other editions. SQL Express is the same product code base.

However, there are a few 'enterprise' level features that are not included in SQL Express. Integration Services and Replication are a couple that come to mind.

You can compare the editions here to get a better idea. (However, for learning how to use SQL Server to support a web site, SQL Express will be a good tool for you.)

SQL Server 2005 Features, Version Comparison
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

|||

Dear Arnie,

Thank you a lot of man all your information was very helpful. So I will start working with it. So in case I am stuck then I will get back to you. Really appreciate you a lot.

|||

Dear Arnie,

Ok I have download both the sql express and also the sql server managment studio express. I all working i can log into my database. The problem is that it is using the windows authentication. I would like to make my own account and create a user name and password. I dont know how to go about ? Can you please help me on this ? Thanks.

|||

You might find the tutorial videos on these sites to be well worth your time and effort.

For SQL Server (especially Lesson Seven):

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#1

For ASP:

http://asp.net/learn/videos/default.aspx?tabid=63

Connecting to Remote SQL Server

Hi all,

I have been encountering problems in connectiing to an sql server remotely. I have a SQL Server installed on a machine with a public IP. I am, running an application on one of my other Servers having a Public IP as well. Now i want to access the SQL Server Database of my first machine from my second machine, but i cannot get connected to it. I give the IP address of the machine in the connection string but it fails. I have also tried it with the port number. What could possibly be the solution for a problem like this ?

Regards.

Hi there,

Maybe It caused by your windows firewall ! the initial status of Windows Firewall is on.
Check the status by turning off your windows firewall on SQL Server machine.

Babak Izadi
LotraSoft Ltd.

|||

By default, SQL Server 2005 does not allows remote connections. To configure SQL Server 2005 to allow remote connections, complete all the following steps:

1.Select the SQL Server Surface Area Configuration tool from the Configuration Tools and then click Surface Area Configuration for Services and Connections option. expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply and Ok for restarting the Database engine service.

2. Try adding sqlservr.exe (present in MSSQL.1\MSSQL\Binn directory for the default instance and \MSSQL$instance_name\Binn directory for the named instance) in the list of allowed firewall exceptions on the Windows 2003 box.

Regards,

Rajesh

|||

Can the two servers ping each other?

from serverA run in a command prompt: "ping serverB"

from serverB run in a command prompt: "ping serverA"

If one of the two fails, you probably have a network problem

Else look at the above post's (Rajesh) instructions

|||Can you check whether there are any issues with WINS issue on the network, ifyou are not able to register with name as explained you might try PING between the boxes and also check the firewall exception in this case to avoid any other barrier.

connecting to remote SQL Server

I have recently taken over as webmaster at my company and need help connecting to our remotely hosted SQL server. The company who is hosting it will charge me "support time" to explain how to connect to it.

The database is used to store data and application information for our customer service center. So it is ALL database driven...

i have installed SQL Server Enterprise Manager on my machine, but have no ideas on where to even being trying to connect. I have the name of the server, password....etc. But do not know where to configure a connection.

ALSO, when i install my Enterprise Manager the first options askes me whether i want to create a new instance of SQL server on a local or remote machine....i am guessing my local machine right...?

any insight or direction is greatly appreciated.

tony.

Don't you just love hosting companies...First - I am assuming that you know the actual SQL Server name and the sa password. Get the IP address of the server (G-d I hope they don't charge you for THAT!) and try to ping the server by name and IP address. If you can ping by name, open Enterprise Manager and expand Microsoft SQL Servers, then right click on SQL Server Group and select New SQL Server Registration. Check the box that says I dont want to use the freakin' wizard anymore. Now enter the name of the SQL Server - not the name of the actual Windows server (unless they are the same - which they will be if it was a standard installation of a single instance). Check Use SQL Server Authentication, then enter the Login Name as 'sa' and the Password as what ever it is. If you hosting company has not blocked port 1434 this should work fine. To keep from writing an episitle, let me know if this works. There is a different procedure is the SQL instance name is different from the Windows Server name.

Brad
Ex Nihilo, Nihil Fit
|||

hahaha

isn't it suppose to be port 1433?

most hosting company have a web-based database administration tools

which you can work on. All you need to know to move on to the development

is to have the connections string.

sqlsql

Connecting to an instance in a cluster

Hello, I just setup a few instances on a cluster. But somehow I cannot
conntect to the instances remotely using SQL Server Management Studio. I can
connect to them locally. I can also connect to the default instance fine. Any
idea?
Thanks!
is the virtual server enabled for remote connections?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:065C8B43-891D-41CC-8602-04FB551CFE34@.microsoft.com...
> Hello, I just setup a few instances on a cluster. But somehow I cannot
> conntect to the instances remotely using SQL Server Management Studio. I
> can
> connect to them locally. I can also connect to the default instance fine.
> Any
> idea?
> Thanks!
|||Yes.
"Hilary Cotter" wrote:

> is the virtual server enabled for remote connections?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:065C8B43-891D-41CC-8602-04FB551CFE34@.microsoft.com...
>
>
|||Did you try to connect to the instance using its IP and port directly?
Linchi
"Yuhong" wrote:

> Hello, I just setup a few instances on a cluster. But somehow I cannot
> conntect to the instances remotely using SQL Server Management Studio. I can
> connect to them locally. I can also connect to the default instance fine. Any
> idea?
> Thanks!
|||Did you configure static or dynamic ports? Is the Browser service running
on all cluster nodes?
Anthony Thomas

"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:05184BB4-026E-4FBF-AD14-8684B121A86F@.microsoft.com...[vbcol=seagreen]
> Yes.
> "Hilary Cotter" wrote:
I[vbcol=seagreen]
fine.[vbcol=seagreen]
|||Browser Service is running on all nodes. Dynamic ports are used by default
and I did not change it. The servers are behind a firewall. I opened port
1434. And port 1433 is open too. I can connect to the default instance fine
remotely and can connect any instances locally.
"Anthony Thomas" wrote:

> Did you configure static or dynamic ports? Is the Browser service running
> on all cluster nodes?
>
> Anthony Thomas
>
> --
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:05184BB4-026E-4FBF-AD14-8684B121A86F@.microsoft.com...
> I
> fine.
>
>
|||No, I did not try to connect using its IP and port. I did not open the
specific port for the named instances. The cluster is behind the firewall and
only Port 1433 and 1434 are open.
Thanks!
"Linchi Shea" wrote:
[vbcol=seagreen]
> Did you try to connect to the instance using its IP and port directly?
> Linchi
> "Yuhong" wrote:
|||You will need to use the SQL Server Configuration Manager to set a static
port for each named instance. You only need to set it once or each
instance on the cluster. You cannot use port 1434 or 1433. Then you open
the firewall for those ports. Note that you will have to restart each SQL
instance after setting the ports so SQL can bind correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:54E0E94E-FAD2-4EDE-86A3-5D63C4E6E5E4@.microsoft.com...[vbcol=seagreen]
> Browser Service is running on all nodes. Dynamic ports are used by default
> and I did not change it. The servers are behind a firewall. I opened port
> 1434. And port 1433 is open too. I can connect to the default instance
> fine
> remotely and can connect any instances locally.
> "Anthony Thomas" wrote:
|||Hi Geoff,
Thanks for your information. There are many IPs in the TCP/IP properties
window in SQL Server Configuration Manager. Which one should I use? Should I
do the lookback adapter IP or do them all? Sorry I am really confused about
this part. The online help is not very clear about this. Thanks again!
"Geoff N. Hiten" wrote:

> You will need to use the SQL Server Configuration Manager to set a static
> port for each named instance. You only need to set it once or each
> instance on the cluster. You cannot use port 1434 or 1433. Then you open
> the firewall for those ports. Note that you will have to restart each SQL
> instance after setting the ports so SQL can bind correctly.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:54E0E94E-FAD2-4EDE-86A3-5D63C4E6E5E4@.microsoft.com...
>

Connecting SQL 2000 Server Remotely

I installed SQL 2000 server on my PC (OS: XP Professional). I wrote a Visual
Basic application that retrieves data from the SQL server that is installed
on my PC. When I start this application at my computer, it works. When I
start this application at another computer in the same network, it fails. It
returns me the following error message:
The application attempted to perform an operation not allowed by the
security policy. The operation required the SecurityException. To grant this
application the required permission please contact your system administrator
,
or use the Microsoft .NET security policy administration tool.
If you click Continue, the application will ignore this error and attempt to
continue. If you click Quit, the application will be shut down immediately.
Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data,
Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089 failed
How do I configure my computer, the SQL server or the application so that I
can connect from another computer in the same network? By the way, I tried t
o
ping the IP address of my computer from the other computer on the same
network, but it fails too. What should I do?
Thank you.there is many things you need to check it before reconfgiure SQL Server:
1) Check Windows Firewall (if there is Service Pack 2 installed -SP2-).
2) Check that you installed SQL Server SP3 *Slammer Worm*.
3) Check the Framework Security in the Administrative Tools (Microsoft .NET
Framework 1.1 Configuration).
"wrytat" wrote:

> I installed SQL 2000 server on my PC (OS: XP Professional). I wrote a Visu
al
> Basic application that retrieves data from the SQL server that is installe
d
> on my PC. When I start this application at my computer, it works. When I
> start this application at another computer in the same network, it fails.
It
> returns me the following error message:
> The application attempted to perform an operation not allowed by the
> security policy. The operation required the SecurityException. To grant th
is
> application the required permission please contact your system administrat
or,
> or use the Microsoft .NET security policy administration tool.
> If you click Continue, the application will ignore this error and attempt
to
> continue. If you click Quit, the application will be shut down immediately
.
> Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data,
> Version=1.0.5000.0, Culture=neutral,
> PublicKeyToken=b77a5c561934e089 failed
> How do I configure my computer, the SQL server or the application so that
I
> can connect from another computer in the same network? By the way, I tried
to
> ping the IP address of my computer from the other computer on the same
> network, but it fails too. What should I do?
> Thank you.

Connecting SQL 2000 Server Remotely

I installed SQL 2000 server on my PC (OS: XP Professional). I wrote a Visual
Basic application that retrieves data from the SQL server that is installed
on my PC. When I start this application at my computer, it works. When I
start this application at another computer in the same network, it fails. It
returns me the following error message:
The application attempted to perform an operation not allowed by the
security policy. The operation required the SecurityException. To grant this
application the required permission please contact your system administrator,
or use the Microsoft .NET security policy administration tool.
If you click Continue, the application will ignore this error and attempt to
continue. If you click Quit, the application will be shut down immediately.
Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data,
Version=1.0.5000.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089 failed
How do I configure my computer, the SQL server or the application so that I
can connect from another computer in the same network? By the way, I tried to
ping the IP address of my computer from the other computer on the same
network, but it fails too. What should I do?
Thank you.
there is many things you need to check it before reconfgiure SQL Server:
1) Check Windows Firewall (if there is Service Pack 2 installed -SP2-).
2) Check that you installed SQL Server SP3 *Slammer Worm*.
3) Check the Framework Security in the Administrative Tools (Microsoft .NET
Framework 1.1 Configuration).
"wrytat" wrote:

> I installed SQL 2000 server on my PC (OS: XP Professional). I wrote a Visual
> Basic application that retrieves data from the SQL server that is installed
> on my PC. When I start this application at my computer, it works. When I
> start this application at another computer in the same network, it fails. It
> returns me the following error message:
> The application attempted to perform an operation not allowed by the
> security policy. The operation required the SecurityException. To grant this
> application the required permission please contact your system administrator,
> or use the Microsoft .NET security policy administration tool.
> If you click Continue, the application will ignore this error and attempt to
> continue. If you click Quit, the application will be shut down immediately.
> Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data,
> Version=1.0.5000.0, Culture=neutral,
> PublicKeyToken=b77a5c561934e089 failed
> How do I configure my computer, the SQL server or the application so that I
> can connect from another computer in the same network? By the way, I tried to
> ping the IP address of my computer from the other computer on the same
> network, but it fails too. What should I do?
> Thank you.

Connecting remotely without any issues.

I already have local and remote connections enabled. What else do I have to
enable? Also, I have installed all components I thought possible. How do I
access or install Data Transforation Project in 2005?
"Geoff N. Hiten" wrote:

> Login locally to the server and run the SQL Surface Area Configuration Too
l.
> SQL 2005 is "secure by default". One aspect of this is the SQL server
> disables all external network connections by default. You can connect
> locally on the box, but not across the network. This allows a DBA to load
> and patch a SQL server without exposing it to network attacks like a SQL
> 2000 system.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVPericzaj wrote:
> I already have local and remote connections enabled. What else do I have t
o
> enable? Also, I have installed all components I thought possible. How do I
> access or install Data Transforation Project in 2005?
>
Do you mean DTS (Data Transformation Services)? That no longer exists
in SQL 2005, it has been replaced with SSIS (SQL Server Integration
Services).
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Connecting remotely without any issues.

I already have local and remote connections enabled. What else do I have to
enable? Also, I have installed all components I thought possible. How do I
access or install Data Transforation Project in 2005?
"Geoff N. Hiten" wrote:
> Login locally to the server and run the SQL Surface Area Configuration Tool.
> SQL 2005 is "secure by default". One aspect of this is the SQL server
> disables all external network connections by default. You can connect
> locally on the box, but not across the network. This allows a DBA to load
> and patch a SQL server without exposing it to network attacks like a SQL
> 2000 system.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVPericzaj wrote:
> I already have local and remote connections enabled. What else do I have to
> enable? Also, I have installed all components I thought possible. How do I
> access or install Data Transforation Project in 2005?
>
Do you mean DTS (Data Transformation Services)? That no longer exists
in SQL 2005, it has been replaced with SSIS (SQL Server Integration
Services).
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Connect to SQL Enterprise 2000

Hi,
I am new to SQL Server can someone please tell me how to connect to SQL
Server Remotely Using VB6 or MS Access 2003.
Using code?
I have this code that I am using to connect Locally
Cn.Provider = "sqloledb"
Cn.Properties("Prompt") = adPromptNever
Cn.Properties("Data Source").Value = "Dell"
Cn.Properties("Initial Catalog").Value = "Namedb"
Cn.Properties("User ID").Value = txtUserName.Text
Cn.Properties("Password").Value = txtPassword.Text
what do I need to add to this to make it work.
Thanks
Dib
You just need to change the "Data Source" property. BTW, to generate the complete connection string, you can use a udl file. Create a text document and call it xxx.udl. Double click the udl file and a wizard will have tabs for each part. Finally open the
udl file in notepad to see the connection string. This is available from MDAC 2.5 onwards I believe.
HTH,
Paul Ibison
|||Thanks for your reply,
Do I need to write any thing in the document of the file I am creating?
Because I copied the string into notepad and saved it as .udl and I am
getting an error saying that it File cannot open ensure that this is a valid
Datalink file
this is what I have in the file
Cn.Provider = "sqloledb"
Cn.Properties("Prompt") = adPromptNever
Cn.Properties("BTW").Value = "Dell"
Cn.Properties("Initial Catalog").Value = "database"
Cn.Properties("User ID").Value = "name"
Cn.Properties("Password").Value = "password"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:30042251-4FA5-4F91-B418-025926016417@.microsoft.com...
> You just need to change the "Data Source" property. BTW, to generate the
complete connection string, you can use a udl file. Create a text document
and call it xxx.udl. Double click the udl file and a wizard will have tabs
for each part. Finally open the udl file in notepad to see the connection
string. This is available from MDAC 2.5 onwards I believe.
> HTH,
> Paul Ibison
|||Dib,
the syntax would change a bit, as you don't need to use the properties
collection, but can put it all together:
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=name;Password=password;Initial Catalog=database;Data Source=Dell"
To get the udl file to open, create a blank textfile named as xxx.udl. Then
double click it to go through the wizard. Inside the udl file (if you open
using notepad) will be the connection string in the format above, or
different for a remote server.
HTH,
Paul Ibison
|||Paul,
Thanks a lot for taking the time to answer my question, I got it to work for
now.
Thanks
Again
Dib
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O38bcfIKEHA.4004@.TK2MSFTNGP11.phx.gbl...
> Dib,
> the syntax would change a bit, as you don't need to use the properties
> collection, but can put it all together:
> cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User
> ID=name;Password=password;Initial Catalog=database;Data Source=Dell"
> To get the udl file to open, create a blank textfile named as xxx.udl.
Then
> double click it to go through the wizard. Inside the udl file (if you open
> using notepad) will be the connection string in the format above, or
> different for a remote server.
> HTH,
> Paul Ibison
>

Connect to SQL Enterprise 2000

Hi,
I am new to SQL Server can someone please tell me how to connect to SQL
Server Remotely Using VB6 or MS Access 2003.
Using code?
I have this code that I am using to connect Locally
Cn.Provider = "sqloledb"
Cn.Properties("Prompt") = adPromptNever
Cn.Properties("Data Source").Value = "Dell"
Cn.Properties("Initial Catalog").Value = "Namedb"
Cn.Properties("User ID").Value = txtUserName.Text
Cn.Properties("Password").Value = txtPassword.Text
what do I need to add to this to make it work.
Thanks
DibYou just need to change the "Data Source" property. BTW, to generate the com
plete connection string, you can use a udl file. Create a text document and
call it xxx.udl. Double click the udl file and a wizard will have tabs for e
ach part. Finally open the
udl file in notepad to see the connection string. This is available from MDA
C 2.5 onwards I believe.
HTH,
Paul Ibison|||Thanks for your reply,
Do I need to write any thing in the document of the file I am creating?
Because I copied the string into notepad and saved it as .udl and I am
getting an error saying that it File cannot open ensure that this is a valid
Datalink file
this is what I have in the file
Cn.Provider = "sqloledb"
Cn.Properties("Prompt") = adPromptNever
Cn.Properties("BTW").Value = "Dell"
Cn.Properties("Initial Catalog").Value = "database"
Cn.Properties("User ID").Value = "name"
Cn.Properties("Password").Value = "password"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:30042251-4FA5-4F91-B418-025926016417@.microsoft.com...
> You just need to change the "Data Source" property. BTW, to generate the
complete connection string, you can use a udl file. Create a text document
and call it xxx.udl. Double click the udl file and a wizard will have tabs
for each part. Finally open the udl file in notepad to see the connection
string. This is available from MDAC 2.5 onwards I believe.
> HTH,
> Paul Ibison|||Dib,
the syntax would change a bit, as you don't need to use the properties
collection, but can put it all together:
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=name;Password=password;Initial Catalog=database;Data Source=Dell"
To get the udl file to open, create a blank textfile named as xxx.udl. Then
double click it to go through the wizard. Inside the udl file (if you open
using notepad) will be the connection string in the format above, or
different for a remote server.
HTH,
Paul Ibison|||Paul,
Thanks a lot for taking the time to answer my question, I got it to work for
now.
Thanks
Again
Dib
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O38bcfIKEHA.4004@.TK2MSFTNGP11.phx.gbl...
> Dib,
> the syntax would change a bit, as you don't need to use the properties
> collection, but can put it all together:
> cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User
> ID=name;Password=password;Initial Catalog=database;Data Source=Dell"
> To get the udl file to open, create a blank textfile named as xxx.udl.
Then
> double click it to go through the wizard. Inside the udl file (if you open
> using notepad) will be the connection string in the format above, or
> different for a remote server.
> HTH,
> Paul Ibison
>