SQL Database Mail Error The mail could not be sent to the recipients because of the mail server failure. Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: 4.3.2 Service not available).

In a client's set up, there is a database mail server that sends mail externally via an Exchange server. Great, no issues with that... Until I wanted to set the test environment to also send test mails... For some reason I would get the following error:

• The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2015-12-05T17:18:36). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: 4.3.2 Service not available).

I double-checked my config's, but no joy. So I went delving into Exchange (this is a Windows 2011 SBS Standard box)...


I took a step back, and asked myself what am I trying to do... I am trying to use Exchange to relay messages to the internet... RELAY, of course!!! SMTP Relay connectors!!!


Quick as a flash,

• I went to Microsoft Exchange -> Exchange On Premise -> Server Config -> Hub Transport
• I double clicked the SMTP Relay connector
• Clicked on Network
• Added the IP address of the test server to "Receive mail from remote servers"
• OK
• Ran the test again

TA-DA!!! It works


Hopefully this will save anyone out in CyberLand countless hours of testing :)


Enjoy


Z

IBM AS400 Linked Server Keeps Crashing SQL Server

I recently had to set up a new Linked Server on SQL Server 2012. It connects to a remote AS400 server using IBM iSeries OLE DB driver (IBM DB2 for I5/OS IBM DA400 Ole DB Provider)

Setting it up was no problem, except, whenever I ran a query, SQL Service would crash when the query finished returning results.

I found loads of good ideas (Switch of DEP, check for Heap Corruption, etc) but nothing worked. Finally I tried each parameter until it worked and got it working!!

To fix:
* Open Linked Servers -> Providers
* Right Click on ABMDA400, Properties
* Tick "Allow InProcess"
* Close the windows

BOOM - It works!!

From what I can gather, using it "out of process" protects SQL from errors on 64-bit machines,

"cannot create the window" message when you open SQL SSMS or Visual Studio

So, on our production machine, we started getting the error "cannot create the window" when most users start SQL Server Management Sudio (SQL 2012) and/or Visual Studio (VS2010).

Short version, Permissions to Machine.Config in C:\Windows\Microsoft.Net\\Config were changed when we installed the AS400 OLEDB drivers (IBM System i Access for Windows V6R1M0), and almost nobody had rights to the file (the only person with effective permissions was me, apparently because I installed it)

I reinstalled .Net,.. No joy... Reinstalled the drivers... no joy... Then I did the most obvious thing I can think of - Give "Everyone" read access on the file itself (right-click, properties, security).

Lo and behold, it works.

Analysis Services Unknown Role that already exists

I was trying to re-deploy a SSAS project using Visual Studio 2010. Every time it deployed, I got the error "the 'role' with 'id' = 'role' doesn't exist in the collection"... Except, we don't have a role called "role".

Googling just led me to things like manually trying to update the .role file and correct it... except WE DON'T HAVE A ROLE CALLED "ROLE"

Desperate times calls for... thinking outside the box!! So I opened the solution in Visual Studio 2013 and deployed... and it worked first time around!!

Not sure why this works, so anyone who sees this post is more than welcome to leave an explanation, but I'll take it as a cheap win, and just say that the problem is fixed :-)

Changing the ID of a cube

So, we decided to do a checkpoint at work, and redeploy the production cubes from 8 months ago. That way, we can run both cubes, and show users what has been improved. however, when trying to deploy the SSAS project, it kept trying to deploy to the production cube. To solve it:

Pre-amble: Assume our cube Database is called JoeSoapSales. 

I changed the name to JoeSoapSales_Feb2014 by renaming it, as well as changing it everywhere I could find in VS. Yet, when I deploy I get a message that the cube "JoeSoapSales" already exists.

Finally, thanks to a lot of googling, I found that, in the solution folder, there is a .database XML file ("JoeSoapSales.Database")... Just change the ID in there using your favourite text editor, and save... Sorted!!

TimeXtender deployment still references old server

We use an ETL tool called TimeXtender (v2014), which is a SSIS wizard.

We recently had to migrate to a new server. After changing the connections via the front-end wizard, saving and deploying the solution, I had an interesting experience. No matter how many times I ran it, my data didn't change. But when I logged into the old server, I was shocked - all my production data had disappeared. Logic told me that TimeXtender had tried to run against my old server, not the new one.

I confirmed all my connections were pointing to the old server.

I approached the vendor, who reconfirmed the basics (did I save, run the wizard, etc... Which I did do).

Eventually, I exported the project to XML, and opened it in notepad... Lo and behold the SSIS server was still set to the old server. I did a quick find/Replace, and imported the project back in... I ran it, and it worked.

---

So, the vendor contacted me this morning. It turns out, that next to the Login credentials button, there is a "Advanced" button, which doesn't change the login properties, but mostly SSIS properties, including server name.

Access Denied to .Net folders when processing a SQL 2012 SSAS Cube

We recently migrated to a new server. The deployment went smooth, up unto the point where we had to process the cube. When trying to process, it failed immediately. The error was: "Errors in the high-level relational engine. The following exception occurred while an operation was being performed on a data source view: Configuration system failed to initialize;An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config' is denied. (C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config)
Access to the path 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config' is denied.."

Access to path? Easy-peasy fix... except it wasn't. I added the service accounts to the admin groups, gave the folders explicit permissions, no joy. Suddenly I felt very silly - such a simple looking problem, and I can't get it sorted? Google yielded no help... So, out of desperation, I yielded to my AD Admin  days, and thought what I would do then... So, to resolve:

• Reset the permissions of the service accounts
• Added the service accounts to a group with the right permissions
• Changed the SQL and SSAS Service accounts to my own account
• Stop and restart the SQL & SSAS Services
• Changed the SQL and SSAS Service accounts back to the Service Accounts
• Stop and restart the SQL & SSAS Services

Voila, it worked :)