SQL express version

On one of our servers is a copy of SQL Server express, it plays "some" role within one of major IT applications. We wanted to check the configuration of the SQL express server, but there doesn't appear to be any sort of management studio installed on the server on which to check settings, i.e. patches, backups, location of DB's, who has access etc.

As I am not a DBA do you need some sort of SQL management studio installed on that server, or can you still connect to that system remotely from a PC with management studio installed.

are there any risks/concerns you as DBA's would have if you found a SQL express version without a management studio installed on the server, if so could you list which.
LVL 3
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pma111Author Commented:
I think I read with express there is no remote access ,so a I assume for effective administration you'd need to remote onto the server itself, fire up management studio express, and manage the system that way?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server Management Studio (SSMS) is a tool provided to perform admin and development tasks. It's not necessary for SQL Server engine works and maybe the person that installed the SQL Server Express Edition forgot or didn't want it installed during the SQL Server setup.

You can run the SQL Server setup again and chose to install SSMS or you can use any SSMS installed remotely in another server or from you PC. Don't forget to check if SQL Server Browser is running in the SQL Server Express machine and if there's no firewalls between the machine that has SSMS installed and the SQL Server Express machine.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think I read with express there is no remote access
Not true.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ste5anSenior DeveloperCommented:
SQL Server Express comes in different flavours.  One of them is without management tools.

But you can download the version with tools.

Then you can run this setup and install the management tools (SSMS) only.

For audits like this, consider using MSBA and the SQL Server Best Practices Analyzer.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You can download a SSMS for free. And it is neither an additional risk to have it installed or not.
If you have remote access (i.e. SQL Expresss can be used over the network) then a SSMS on any other PC works great. You'll need proper authentication, though, but usually you know the SA password ;-(.

A locally installed SSMS has the advantage that you can get into the instance even if anything else fails.
0
pma111Author Commented:
So by default all SQL Server express installations can be accessed remotely via any machine with SSMS installed?
0
pma111Author Commented:
our admin said the express DB cannot be accessed remotely as the server "does not include any tools", does this make any sense to SQL experts?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. Only check what I said above about SQL Browser service and firewall if the server is behind one.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
our admin said the express DB cannot be accessed remotely as the server "does not include any tools", does this make any sense to SQL experts?
Sure not. You just need to know the server name and try to connect to in from any remote SSMS. The default instance name should be ServerName\SQLEXPRESS and the default port 1433.
0
ste5anSenior DeveloperCommented:
Well, he be correct. SQL Server Express in its default installation does not accept remote connections. This does not depend on the installed tools. Just on the SQL Server surface configuration.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The default for SQL Server Express is (has been) that remote access is not enabled. But if so, only applications running on the server can access the DB. If you can use SQL from another PC against that Express, you can use a management tool like SSMS remotely. Whether SSMS is installed locally or not does not matter.
0
pma111Author Commented:
port 1433 is not open!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you confirm that SQLExpress instance is using port 1433 and it's behind a firewall?
Don't forget to check if SQL Browser service is running.
0
pma111Author Commented:
I thought remote access issue was the case, was confused when the other expert above said that was not true and remote access is available..
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
It would really help if you could just confirm whether you can access the Express DB from remote, or do that only on the server running Express!?
0
pma111Author Commented:
in this case I am not trying to connect myself, the DBA's have made a comment that remote access is not available without installing SSMS on the server itself. I was also wondering for a comprehensive list of concerns/risks you would have if you found an "unaccessible" and therefore unmanaged SQL express installation in your environment. Surely this is a big problem?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Did you read our comments? No, it isn't a big problem. No, installing SSMS on the server is not related to having remote access to SQL Server. No, there are no risks or concerns for installing a SSMS on the server - it does not matter, security-wise.
The surface tool is used to allow remote access. This is a concern, but only very minor issue.
0
pma111Author Commented:
your not understanding what I am asking.

I am saying "is it a risk that nobody in a network can even access a database server to monitor/manage it", I no the answer is yes, but I was after some examples of what issues can arise when you have unmanaged database servers.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
There is no risk. Having a completely unmonitored SQL Server instance is, in particular with Express, a potential performance issue, that's all. Statistics are created and maintained automatically, if set up /and it is the default). But detailled analysis is impossible without access by tools.

There is also the question about performing backups ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.