Voice of the DBA

Tag: sql server

  • Securing Your Instances

    I wrote a post about finding the port number of a SQL instance using PowerShell. Almost immediately I was taken to task by someone that noted port scanners can easily find SQL ports, so it’s silly to move off 1433. Just use it because applications expect it. I can see that, and changing ports doesn’t provide much security, but it does provide some obscurity, which may or may not be helpful. Certainly this also creates administrative and support burdens for a system. If you want other opinions, there’s a Q&A on Stack Exchange for this topic as well.

    Tom LaRock wote a post that this can be a way to obfuscate your database, prevent simple default connections, and potentially detect security issues before they become a problem. I tend to learn towards this approach as well, because these small changes can potentially provide a little protection. A port scan is quick, but firewalls are getting better at detecting these. Certainly criminals get smarter, but changing a port number isn’t intended to stop everyone. If it stops a few, then that’s fine.

    Security comes about because of layers, and limitations, and will never be perfect. There will always be ways that someone can get around security, but the more layers, the more obstacles you place in their way, the fewer people that will overcome all of them. I’d also note that plenty of attacks come from vandals. People that are just bored and looking for easy ways to get into a system. Most of these people are using pre-written scripts and programs that try out defaults. These aren’t concerted, directed attacks. They’re attacks from boredom.

    I’m curious today how many of you think a little bit of security from obfuscating ports is a good idea. I wouldn’t recommend the SQL Browser be open, but that legitimate connections know about the port needed, and use that in their connection strings. I think it helps, and if this limits some attacks, especially inside-the-firewall virus attacks from trusted machines, I think it’s worth doing.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Not Excited by Linux

    You’re not excited by Linux. At least most of you in this community aren’t excited. A recent poll we ran showed that nearly two thirds of the people answering aren’t interested in even evaluating SQL Server on Linux. That’s an interesting result because in some groups I’ve spoken with, quite a few people are excited by the prospect of using a different host platform for their database.

    When Microsoft first announced they were going to run SQL Server on Linux, I was excited, mostly because I think this means more applications and organizations would consider using SQL Server, which I think is a fantastic platform. It’s been the platform of choice for my entire career. However, from a business perspective, I wasn’t sure if this was a great idea. After all, Microsoft may reduce their revenue from Windows licenses if a large number of people moved to Linux for their underlying OS.

    Does the OS matter? I don’t know if it does. I’m not sure how much revenue comes in from Windows Server licenses for hosting SQL Server. I’m sure it’s a significant number, but will the number decline substantially if some people move to Linux from Windows for their SQL Servers? Perhaps, but I wouldn’t think many people make that move.

    Instead, I think the people excited by Linux hosting SQL Server are those that already are deeply committed to the Linux platform and avoid SQL Server because they don’t want to run Windows servers. There will be DB2 and Oracle databases that might move to a much less expensive SQL Server license. Certainly I expect lots of (free) Express editions installed by developers, which will allow them to gain experience, and perhaps be more comfortable in the future with SQL Server as a data platform.

    It’s hard to know if the move to Linux will result in a big change to Microsoft’s bottom line, but I do think it opens up many new opportunities for us, as data professionals, to find work. With the way that SQL Server works and the seamless way the Linux version seems to behave, I don’t even think most of us need to know much about Linux to develop or manage an instance on that platform.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Please Fix This–T-SQL Tuesday #86

    tsqltuesdayIt’s T-SQL Tuesday time, and this month is an interesting topic. The invite comes from Brent Ozar, and it’s designed to get you to look at SQL Server. I spent some time thinking about this one and getting a post together.

    You can participate as well. Just write a post on your blog, the second Tuesday of the month, link to the invite or leave a comment on their post. If you want to host, contact Adam Machanic.

    Better Checks

    This Connect item was submitted by Erland Sommarskog, and I think it’s one that is worth spending development time on. The title is Add optional checks for more robust development, and I think it’s a great idea. I’m a big fan of testing and ensuring that you meet standards, that silly mistakes aren’t being made in code, and in general, finding ways to improve developer productivity.

    I know that getting errors or failing tests are a pain, but these should drive you to change behavior. If you constantly code “select *”, then you want to get out of the habit. Tests, standards, or other checks are one way to do this.

    This suggestion from Erland, which has quite a few votes, is a good one. Getting some sort of warning from deferred name resolution, is a good thing. Having the ability to prevent, or ignore some of the issues, is important. I’d really like to see all of these checks implemented, with the ability to turn them on/off as needed.

    These don’t take the place of unit tests with something like tSQLt or automated testing, but they are a good start to helping us produce better software.

    If you agree, go vote , and perhaps we can get this implemented.

  • SQL Server is Getting More Popular

    At the db-engines.com site, SQL Server was named the DMBS of the year. This was the site’s choice based on a measure of how popularity changed from Jan 2016 -Jan 2017. Apparently SQL Server grew in popularity in a number of ways, including job offers and LinkedIn profiles, but also with various search engine metrics and forum discussions. A few people have noted that SQL Server isn’t the most popular DBMS, as Oracle and MySQL are above it in January 2017. However, the change in score was dramatic for SQL Server, with MySQL a close second.

    Perhaps Microsoft’s embracing of open source is a reason why SQL Server is growing in popularity. That’s what some people think. With the announcement of SQL Server on Linux, as well as the regular commitments they make to Github, where Microsoft is the largest contributor. Microsoft has had a place for open source projects, CodePlex, for years, but now they have an open source site as well where they have numerous announcements and resources about how one might integrate some of the open source products with their closed source products.

    I certainly think the popularity of SQL Server is increasing as it grows to encompass more and more features. Compared to Oracle, SQL Server is a much less expensive, and it integrates well with the .NET stack of programming tools. Microsoft has made strides to make it easier for connections from Java and Python code to SQL Server. I’ve run across quite a few people using Redis, ElasticSearch, and more in applications along with SQL Server, which implies it’s becoming more acceptable and easier to integrate different data platforms than in the past.

    SQL Server has been a part of my career for over two decades, and in that time I’ve seen it become more and more popular, more useful, and easier to integrate into a wide variety of applications. I expect this will continue with the Azure SQL Database and Azure SQL Data Warehouse, as well as the Azure Data Lake. There might not be as many installations of SQL Server as Oracle or MySQL, but I think it’s the best database platform for me.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.9MB) podcast or subscribe to the feed at iTunes and Libsyn.