Skip to main content

Test connection to remote SQL Server Database from an Application server





While aiming to test whether a SQL connection is succeeding between an Application server and a remote Database Server, it is often not possible to install SQL Server Management Studio (SSMS) or Microsoft Command Line Utilities (MsSqlCmdLnUtils) due to the locked down nature of the targets, particularly in test and production environments.

A lightweight approach that worked for me recently, makes use of components that have been a part of windows boxes for a long time, albeit different levels of database driver support as the components have evolved, the Microsoft Data Access Components (MDAC).

MDAC provide a Universal Data Link, which can be configured using a common user interface for specifying connection properties as well as testing the connection.





 Data Link properties dialog box



Get started – Create a simple text file

Simply create a .txt file anywhere on your system



  • rename the extension to .udl





Double click the Universal Data Link file (.udl)


We are then presented with the Data Link Properties dialog, which will look very familiar.

Using either Windows Integrated Security or a specific Username / Password combination the connection to the database can be tested.




You can also save your configuration settings you have applied by just selecting ‘OK’ on the properties dialog, useful for reoccurring scenarios, more complex configurations or just as a handy utility for future reference.



Using this approach, i was able to determine very quickly that the password for a test environment database had been changed in just a few minutes.

References

https://en.wikipedia.org/wiki/Microsoft_Data_Access_Components
https://msdn.microsoft.com/en-us/library/e38h511e(v=vs.71).aspx

Checkout some other musings via my blazor.net and azure blog here carlrandall.net



Feel free to contact me via my site AssemblySoft to discuss any ways i can help with your next project or checkout some other musings via my blazor.net and azure blog here carlrandall.net


Popular posts from this blog

Azure Devops - Pull Request Merge Conflicts

Before a Git pull request can complete, any conflicts with the target branch must be resolved. Out of the box, at the time of writing this article, Azure DevOps requires this to be resolved locally. Following best practices to not allow direct commits to our release/master branches further exasperates the problem as we need to effectively clone the branch or go with a rebase approach, both of which break the natural flow of resolving the conflicts as part of the pull request. With this extension, from the Microsoft DevLabs team via the Marketplace, you can resolve these conflicts online, as part of the pull request process, instead of being forced to break flow and resolve locally. Online Experience After adding the extension the new conflicts tab is visible which enables conflict resolution in the familiar side by side review page as shown below: Really nice extension, which should make resolving merge conflicts a much more straightforward part of the DevOps

Simple Git branching strategy for release cycles

Coming up with a branching strategy that works well can be challenging when working with multiple developers and managing release cycles. A simple approach is presented here to manage release cycles, with a small to medium sized team of developers while still being able to react to production issues and fix bugs. The primary goal being to isolate work streams without impacting development progress. Background Git does not enforce any particular strategy when it comes to branching which is partly what makes it such a great and flexible repository. The problems start to arise though as you move into different stages of your development process. As an example, you have a release almost complete but don’t want to impede progress on the upcoming release cycle which is where the majority of effort is required. The Basic Approach The focus is around producing a release while still being able to react to hotfixes or production issues without impacting on going development of featu

Azure DevOps Authorisation

Managing whether an identity has access to a given  service, feature, function, object, or method in Azure DevOps comes down to authorisation. Fortunately, by default, the DevOps permissions are set in such a way to enable you to focus on the job at hand, DevOps. Loosely translated this means 'don't get in my way'. My experience is that the Azure DevOps team have done a good job at this, enabling you to crack on developing, building, testing and releasing without much hindrance. Working with relaxed permissions is great when you are the owner and possibly either a one man band or small team but as soon as we need to consider larger teams, varying roles with approvals and degrees of access, authorisation becomes a real concern. I was recently involved in a project utilising offshore developers where trust was a concern and a number of specific teams handling specific roles needed to come together to approve a set of pipelines.  This article is a pick of findings a