SQL 2005 Tools

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MF - 14 Jul 2008 11:50 GMT

Hello All,

I am not conversant with SQL but I need to know what tools or apps can be
used to connect to SQL Server 2005 on SBS 2003 Premium or what methods there
are for connecting to it. Basically, my manager (who knows nothing about
databases) demands that he needs to connect to the database being made
available to a developer). The database has been created and I am able to
successfully telnet to it remotely. How can I have the manager connect to it?
And what else do I need to do to let the developer connect and do development
work on it?

Thanx.

Reply to this Message

Andrew J. Kelly - 14 Jul 2008 13:54 GMT

Connecting to the database can be done via ODBC, OLEDB etc. from just about
any client interface. The question is what does this developer need to do?
The tool of choice to manage a SQL2005 database, write TSQL etc. is the SQL
Server Management Studio (SSMS). You are telneting to the server that the
database instance is hosted on, not the database instance itself. I don't
know what comes with SBS but I have to assume SSMS is included and if the
client tools were loaded you can find it in the SQL Server folder in the
start menu. You can load the client tools (SSMS & others) on other machines
as well in order to connect to SQL Server.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Hello All,
>
[quoted text clipped - 11 lines]
>
> Thanx.

Reply to this Message

MF - 14 Jul 2008 16:15 GMT

Thanx for your response. Yes, SSMS is included in SQL 2005 on SBS. How do I
load SSMS (and what other tools) on client machines to connect to a specific
database created on the default instance?

Thanx.

> Connecting to the database can be done via ODBC, OLEDB etc. from just about
> any client interface. The question is what does this developer need to do?
[quoted text clipped - 21 lines]
> >
> > Thanx.

Reply to this Message

MF - 14 Jul 2008 16:20 GMT

In addition, the developer needs to develop a database with Office Access for
the company I believe. I have not been able to speak with her yet, but I was
told this by the manager.

> Thanx for your response. Yes, SSMS is included in SQL 2005 on SBS. How do I
> load SSMS (and what other tools) on client machines to connect to a specific
[quoted text clipped - 27 lines]
> > >
> > > Thanx.

Reply to this Message

William Vaughn (MVP) - 14 Jul 2008 18:33 GMT

Ah, I would install the Client tools from SQL Server Express (Advanced
Services Edition) to get to the server, but I'm not sure this is such a good
idea unless you don't really care about data security or integrity. The fact
that they want to create an Access application against the DBMS is troubling
as well. If it's not properly implemented, a clumsy or inept developer (or
manager) can wreck the entire database in a heartbeat--with any client tool.

Drop me a line if you want to talk this over in more detail. I would also
suggest picking up a copy of my book. It's targeted at companies like yours.

> In addition, the developer needs to develop a database with Office Access
> for
[quoted text clipped - 49 lines]
>> > >
>> > > Thanx.

Signature

__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

Reply to this Message

MF - 14 Jul 2008 18:47 GMT

I have tried my best to warn the manager who authorized this sort of work. I
made the implications clear to him and advised him to have the developer do
any development on their server and port over for deployment when done. He
said NO, that he prefers developing directly on the server that currently
runs the company's business. At least I have this in black and white on my
email so it won;t be an issue of blaming me for anything.

Anyway, I downloaded and installed the SSMSE
(http://www.microsoft.com/downloads/details.aspx?FamilyID=6053c6f8-82c8-479c-b25b
-9aca13141c9e&displaylang=en)
and I need to know how to use this thing to connect to the database remotely.
I have already forwarded TCP port 1433 and UDP port 1434 to the SQL server
and I am able to telnet to port 1433 successfully.

How do I configure SSMSE to connect to the database in a re mote office via
TCP/IP?

Thanx.

> Ah, I would install the Client tools from SQL Server Express (Advanced
> Services Edition) to get to the server, but I'm not sure this is such a good
[quoted text clipped - 59 lines]
> >> > >
> >> > > Thanx.

Reply to this Message

William Vaughn (MVP) - 14 Jul 2008 19:03 GMT

Ah, as I see it, your manager is begging for a disaster. Consider that SQL
Server is initially configured to prevent access from the web or even the
network. You'll need to enable the ports and protocols using the SQL Server
Surface Area configuration tool. SSMS can't be "configured"--all you get to
provide is a connect string to point to the server instance and pass the
credentials (and little else). Configuration is all done on each server
instance.

I rarely (okay, never) recommend exposing a SQL Server on the WWW to be
accessed by an IP address.
I never recommend doing development on a production server. I know by
experience that it's VERY simple to delete or mangle the database with no
hope of recovery.
I suggest building a system that has a copy of the production database for
your manager to play with. Once the application is working, then you can try
to access the production DB.

If I was working for you as a consultant and came across a manager with this
attitude, I would leave. I would not want my name associated with a highway
bridge as the manager started pulling out bolts.

> I have tried my best to warn the manager who authorized this sort of work.
> I
[quoted text clipped - 95 lines]
>> >> > >
>> >> > > Thanx.

Signature

Reply to this Message

MF - 14 Jul 2008 19:19 GMT

I am a consultant for the company and this just does not make sense. Not only
does he want the developer to work on the server, he himself is demanding
access to SQL management tools to access it too. He knows nothing about such
things yet he requested the password from me to access the server and poke
around the database and doing all sorts. I observed that his username has now
been added to system databases and he created a new database as well. The
server has remained pristine until he requested the password recently and
went into it the server.

Anyway, what string do I use to allow connections to a specific database in
the default instance and where do I put the string in what tool?

Thanx.

> Ah, as I see it, your manager is begging for a disaster. Consider that SQL
> Server is initially configured to prevent access from the web or even the
[quoted text clipped - 116 lines]
> >> >> > >
> >> >> > > Thanx.

Reply to this Message

William Vaughn (MVP) - 15 Jul 2008 18:06 GMT

It's a catch-22 sort of deal. In order to grant access to additional (specific) Windows domain accounts or to specific SQL Server login IDs you need to have System Administrator (SA) access to the database. Using these credentials you can use SSMS to connect to the server (instance) and make changes to the security settings including the logins. Notice that I have added specific domain users to the list of acceptable Logins. Each of these has properties that grant/deny access to specific system and user databases. Ah, setting up login authentication is not something to be taken lightly. Once you grant anyone (even your manager) rights to the database they can potentially wreck it, expose it to the outside world or just make it a living nightmare to keep stable... just don't tell me that I didn't warn you.

> I am a consultant for the company and this just does not make sense. Not only
> does he want the developer to work on the server, he himself is demanding
[quoted text clipped - 130 lines]
>> >> >> > >
>> >> >> > > Thanx.

Signature

Reply to this Message

MF - 15 Jul 2008 18:33 GMT

I have warned the manager again via email of the risk of doing this and he
responded that he understands the risk but will go ahead with it. So I can
say I am immune from any consequences with email evidence in black and white.

Can you please point me to tools I can recommend to the manager to connect
to a specific database in the default instance over the internet as well as
connection strings used for specific tools.

Thanx.

> It's a catch-22 sort of deal. In order to grant access to additional (specific) Windows domain accounts or to specific SQL Server login IDs you need to have System Administrator (SA) access to the database. Using these credentials you can use SSMS to connect to the server (instance) and make changes to the security settings including the logins. Notice that I have added specific domain users to the list of acceptable Logins. Each of these has properties that grant/deny access to specific system and user databases. Ah, setting up login authentication is not something to be taken lightly. Once you grant anyone (even your manager) rights to the database they can potentially wreck it, expose it to the outside world or just make it a living nightmare to keep stable... just don't tell me that I didn't warn you.
>
[quoted text clipped - 132 lines]
> >> >> >> > >
> >> >> >> > > Thanx.

Reply to this Message

William Vaughn (MVP) - 15 Jul 2008 22:22 GMT

Well, it depends on the DB and the installation. As I said, SQL Server is
configured to prevent this kind of connection (for lots of good reasons).
You'll need to determine which port(s) the instance is using. You can't
depend on the default ports as SS now uses dynamic port allocation which
must be disabled to expose a specific port to the web. Once you have that
determined you can use a TCP/IP-specific connection string that addresses
the (default) or any instance.

Here is a copy of a typical TCP/IP connection string from
ConnectionStrings.Com. Note that it assumes port 1433 is assigned to the
instance. Each instance will have its own port. Use the SQL Server
Configuration Manager to determine or set the port assignments. You might
also have to start the SQL Browser service on the server as well. Of course,
this assumes that the server has enabled TCP/IP protocol and the ports in
the first place. No, I don't expect you'll be able to use Domain (SSPI)
security with this approach so you'll need to setup a Login for your
manager. DO NOT let him log on as SA. If he insists on that, get it in
writing so you can defend yourself to his manager. Personally, I would also
CC his manager to make sure that he/she knows that your manager is
jeopardizing the database and all it contains.

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial
Catalog=myDataBase;User ID=myUsername;Password=myPassword;

DBMSSOCN=TCP/IP. This is how to use TCP/IP instead of Named Pipes. At the
end of the Data Source is the port to use. 1433 is the default port for SQL
Server.

> I have warned the manager again via email of the risk of doing this and he
> responded that he understands the risk but will go ahead with it. So I can
[quoted text clipped - 208 lines]
>> >> >> >> > >
>> >> >> >> > > Thanx.

Signature

Reply to this Message